gfk76
asked on
SQL Left join on same table
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.Invoice no,t3.Invo iceno from Invoices t
left join Invoices t1 on (t.customerid=t1.customeri d and t.Invoiceno=t1.Invoiceno and t1.lineno=1)
left join Invoices t2 on (t.customerid=t2.customeri d and t.Invoiceno=t2.Invoiceno and t2.lineno=2)
left join Invoices t3 on (t.customerid=t3.customeri d and t.Invoiceno=t3.Invoiceno and t3.lineno=3)
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,
left join Invoices t1 on (t.customerid=t1.customeri
left join Invoices t2 on (t.customerid=t2.customeri
left join Invoices t3 on (t.customerid=t3.customeri
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
p.s. don't use reserved words as object names
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:
CREATE TABLE #Invoices
(
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
GROUP BY I.[LINENO];
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
GROUP BY I.[LINENO];
SELECT @SqlPivotColumns += REPLACE(',[{0}]', '{0}', CAST(I.[LINENO] AS VARCHAR(MAX)))
FROM #Invoices I
GROUP BY I.[LINENO];
SET @SqlPivot = REPLACE(@SqlPivot, '{0}', STUFF(@SqlSelectColumns, 1, 1, ''));
SET @SqlPivot = REPLACE(@SqlPivot, '{1}', STUFF(@SqlPivotColumns, 1, 1, ''));
EXECUTE ( @SqlPivot );
DROP TABLE #Invoices;
p.s. don't use reserved words as object names
ASKER
Thank you very much for Your solution.
Open in new window