Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Left join on same table

Posted on 2014-11-06
4
Medium Priority
?
392 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 53

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 36

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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
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…
Suggested Courses

581 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