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.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)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

ste5anSenior DeveloperCommented:
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
gfk76Author Commented:
Thank you very much for Your solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.