SQL Left join on same table

Posted on 2014-11-06
Last Modified: 2014-11-06
Hi Experts!

I have big some issues with doing a SQL query with multiple LEFT JOIN's on the same table.

I Have a table INVOICES that looks like this

Customerid      Invoiceno      lineno
1234            98765            1
1234            98800            2
4321            98801            1
3241            98802            1
4321            98803            2
1234            98804            3

I am trying to create a query that will list each customer on 1 line.
See below.
The database I use is iAnywhere (Sybase)

Customerid      Invoice1      Invoice2      Invoice3
1234            98765            98800            98804
4321            98801            98803            NULL
3241            98803            NULL            NULL

In order to acomplish this I have tried with the following Query, but it still list up 1 line per invoice no.
Is someone able to seee where I go wrong?

select t.customerid,t1.Invoiceno,t2.Invoiceno,t3.Invoiceno from Invoices t
left join Invoices t1 on (t.customerid=t1.customerid and t.Invoiceno=t1.Invoiceno and t1.lineno=1)
left join Invoices t2 on (t.customerid=t2.customerid and t.Invoiceno=t2.Invoiceno and t2.lineno=2)
left join Invoices t3 on (t.customerid=t3.customerid and t.Invoiceno=t3.Invoiceno and t3.lineno=3)
Question by:gfk76
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 40425693
no need for left join ....
assuming that "lineno" is indeed 1 , 2 , 3 etc:

select t.customerid
, max( case when t.lineno = 1 then t.Invoiceno end) invoice1
, max( case when t.lineno = 2 then t.Invoiceno end) invoice2
, max( case when t.lineno = 3 then t.Invoiceno end) invoice3
from Invoices t
group by t.customerid

Open in new window

in case lineno is not 1,2,3 but any values , we can use a subquery to generate these numbers with ROW_NUMBER() function
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40425711
I think you can use PIVOT functionality here:
select Customerid, [1] as invoice1, [2] as invoice2, [3] as invoice3
  select *  from Invoices 
) t
  for lineno in ([1], [2], [3])
) PivotName

Open in new window

LVL 33

Expert Comment

ID: 40425759
You need a pivot. Either the classic way as Guy wrote or using the PIVOT operator as Vitor wrote.

Both solutions require that the number of pivoted columns is constant and known when you write your query. When your line number is dynamic, then you need normally a dynamic SQL like:

      Customerid INT ,
      Invoiceno INT ,
      [LINENO] INT

INSERT  INTO #Invoices
VALUES  ( 1234, 98765, 1 ),
        ( 1234, 98800, 2 ),
        ( 4321, 98801, 1 ),
        ( 3241, 98802, 1 ),
        ( 4321, 98803, 2 ),
        ( 1234, 98804, 3 ),
        ( 3210, 98823, 5 );

-- Static SQL
SELECT  I.Customerid ,
        MAX(CASE WHEN I.[LINENO] = 1 THEN I.Invoiceno END) AS invoice1 ,
        MAX(CASE WHEN I.[LINENO] = 2 THEN I.Invoiceno END) AS invoice2 ,
        MAX(CASE WHEN I.[LINENO] = 3 THEN I.Invoiceno END) AS invoice3
FROM    #Invoices I
GROUP BY I.Customerid;

SELECT  P.Customerid ,
        P.[1] AS invoice1 ,
        P.[2] AS invoice2 ,
        P.[3] AS invoice3
FROM    #Invoices I PIVOT
( MAX(Invoiceno) FOR [LINENO] IN ( [1], [2], [3] ) ) P;

-- Dynamic SQL
DECLARE @SqlMax VARCHAR(MAX) = 'SELECT  I.Customerid, {0} FROM #Invoices I GROUP BY I.Customerid;';
DECLARE @SelectExpressions VARCHAR(MAX) = '';

SELECT  @SelectExpressions += REPLACE(',MAX(CASE WHEN I.[LINENO] = {0} THEN I.Invoiceno END) AS invoice{0}', '{0}', CAST(I.[LINENO] AS VARCHAR(MAX)))
FROM    #Invoices I

SET @SqlMax = REPLACE(@SqlMax, '{0}', STUFF(@SelectExpressions, 1, 1, ''));

EXECUTE (@SqlMax);

DECLARE @SqlPivot VARCHAR(MAX) = 'SELECT P.Customerid, {0} FROM #Invoices I PIVOT ( MAX(Invoiceno) FOR [LINENO] IN ( {1} ) ) P;';
DECLARE @SqlSelectColumns VARCHAR(MAX) = '';
DECLARE @SqlPivotColumns VARCHAR(MAX) = '';

SELECT  @SqlSelectColumns += REPLACE(',P.[{0}] AS invoice{0}', '{0}', CAST(I.[LINENO] AS VARCHAR(MAX)))
FROM    #Invoices I

SELECT  @SqlPivotColumns += REPLACE(',[{0}]', '{0}', CAST(I.[LINENO] AS VARCHAR(MAX)))
FROM    #Invoices I

SET @SqlPivot = REPLACE(@SqlPivot, '{0}', STUFF(@SqlSelectColumns, 1, 1, ''));
SET @SqlPivot = REPLACE(@SqlPivot, '{1}', STUFF(@SqlPivotColumns, 1, 1, ''));

EXECUTE ( @SqlPivot );

DROP TABLE #Invoices;

Open in new window

p.s. don't use reserved words as object names

Author Closing Comment

ID: 40425803
Thank you very much for Your solution.

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question