Solved

SQL Left join on same table

Posted on 2014-11-06
4
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

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 50

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 34

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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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