Joining Problem

Hi

I am having some problme with a join
There is two tables a customer and orders
The customer table has a unique list of customer
The orders table fills up as they come in

Customer Table
ID Name
1 Cust1
2 Cust2
3 Cust3

Orders Table
ID Customer Product
1 1 Tea
2 1 Coffee
3 1 Milk
4 2 Tea
5 2 Coffee

So there can be multiple orders for one customer

How would I show a list of customer who have never
ordered 'Milk'

This usually does it for me:

select c.ID, c.Name
from Customers c left join Orders o
on c.ID = o.Customer
where c.ID not in
(select o.Customer from Orders o
where o.Product = 'Milk')

It's not the prettiest code but it should work for you.

|||

I think this should be simpler and the output customer will be unique ( no double record )

select c.ID, c.Name
from Customers
where c.ID not in
(select o.Customer from Orders o
where o.Product = 'Milk')