SQL Left join on same table
Posted on 2014-11-06
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.
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)