samedi 15 août 2015

SQL Left join with where clause

I have the following tables.

Table1
ID          CustomerName             CustomerAddress
1            Joe Bloggs                123 The Street
2            Joe Bloggs Senior         345 The Close
3            Joe Bloggs Jnr            1 The Road


Table2
Table1.ID        Item            
1                 Toaster
1                 Kettle
1                 Microwave
3                 Toaster
2                 Iron
2                 Kettle
2                 Microwave
2                 Slow Cooker

What i want is a list of customers and other products they have purchased, if they have purchased a toaster.

I have used the following SQL query.

SELECT CustomerName, Item from Table1 JOIN Table2 ON LEFT JOIN TABLE1.ID = TABLE2.Table1.ID

Which obviously gives me all the items brought with customer name.

If i try and put a where clause in

SELECT CustomerName, Item from Table1 JOIN Table2 ON LEFT JOIN TABLE1.ID = TABLE2.Table1.ID WHERE Item == Toaster 

i obviously don't get the other items

I am totally stumped how to do this.

Thanks

Aucun commentaire:

Enregistrer un commentaire