conditional LEFT JOIN

[AlexStacey]

Hi,

I'm having trouble with this and wondered if anyone had any thoughts on this...

table1
customerID
customerName

table2
orderID
customerID
productID

I want a list of all the customers, and if they placed an order. This works fine:

SELECT table1.customerName, table2.orderID
FROM table1
LEFT JOIN table2
ON table1.customerID = table2.customerID


But how do I get a list of all of the customers, and if they placed an order with a productID of 5?? I've tried adding

WHERE table2.productID = 5

to the end but that will only get the customers that have ordered, not the whole list of customers.

Thanks in advance,

alex =]

[russell]

what db are u using?

[russell]

this works in sql server.

SELECT a.customerID, a.customerName,
Case isNull(c.customerID, 0)
WHEN 0 Then 0
ELSE 1 End as boughtProd5
FROM table1 a
LEFT JOIN
table2 b
On a.customerID = b.customerID
LEFT JOIN (
SELECT distinct customerID
FROM table2
WHERE productID = 5
) c
On c.customerID = a.customerID

[aussie girl]

Why do you need a left join on something so simple? This should give you all the info whether they have ordered something or not


SELECT table1.customerName, table2.orderID, table2.productID
FROM table1, table2
WHERE table1.customerID = table2.customerID

[russell]

that will filter out the customers who aren't in the order table. also, should use correct join syntax, even if an inner join insted of "From table1, table2"

[AlexStacey]

Hi,

Thanks for your replies. =]

I'm using a MySql DB with PHP. I'm not sure if the CASE thing will work in MySQL but I'll try it and let you know.

Alex =]

[maitre-d]

I am a bit new to this and so am not sure if it'll work but what about INNER LEFT JOIN.

I think I am right in thinking that this will include all names from one table and those from another based on the WHERE statement.

Try this and see how it goes:

SELECT table1.customerName, table2.orderID
FROM table1 INNER LEFT JOIN table2 ON table1.customerID = table2.customerID
WHERE table2.productID = 5

Please accept my apologies in advance if this doesn't work out OK.

[prati_0510]

SELECT distinct Table1.CustName, Table2.OrderId

FROM Table1 full OUTER JOIN
Table2 ON Table1.CustID = Table2.CustId
WHERE (Table2.ProdID = '5')
union
Select Table1.CustName,'0' as OrderID from table1 where CustName not in
(SELECT distinct Table1.CustName

FROM Table1 full OUTER JOIN
Table2 ON Table1.CustID = Table2.CustId
WHERE Table2.ProdID = '5')

[prati_0510]

i ve tried this in MS Sql Server .....
hope this works for u.... alll the best

SELECT distinct Table1.CustName, Table2.OrderId

FROM Table1 full OUTER JOIN
Table2 ON Table1.CustID = Table2.CustId
WHERE (Table2.ProdID = '5')
union
Select Table1.CustName,'0' as OrderID from table1 where CustName not in
(SELECT distinct Table1.CustName

FROM Table1 full OUTER JOIN
Table2 ON Table1.CustID = Table2.CustId
WHERE Table2.ProdID = '5')