Solved

SQL Left join on same table

Posted on 2014-11-06
4
300 Views
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)
0
Comment
Question by:gfk76
4 Comments
 
LVL 142

Accepted Solution

by:
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
0
 
LVL 46

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
from
(
  select *  from Invoices 
) t
pivot
(
  max(Invoiceno)
  for lineno in ([1], [2], [3])
) PivotName

Open in new window

0
 
LVL 33

Expert Comment

by:ste5an
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:

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;

Open in new window



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

Author Closing Comment

by:gfk76
ID: 40425803
Thank you very much for Your solution.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

912 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now