How to filter the correct orders with MySQL (One order contains many lines)

Question

I have to filter orders which do not have a specific product.

It is simple, but the problem is that every order could have many lines, containing different products.

Here is an example of my data:

ID | Product | Customer      | Quantidy | Date
 1 | Apple   | Alex Sorensen | 3        | 17.4.2009
 2 | Orange  | Alex Sorensen | 1        | 17.4.2009
 3 | Lime    | Alex Sorensen | 4        | 17.4.2009
 4 | Apple   | Fred Jonsson  | 1        | 30.5.2010
 5 | Lime    | Fred Jonsson  | 7        | 30.5.2010
ect...

Lines with the same date and the same customer are for the same order.

How can I find all the orders which do not have (for example) Orange in their order?

---

My own (not working) MySQL-code:

SELECT o.ID, k.Customer, o.Quantidy, p.Product, o.Date
FROM Products p, Orders o, Customers c
WHERE p.ID = o.ID
AND k.Customer = o.Customer
AND p.Product NOT IN ('Orange')
GROUP BY o.Date
ORDER BY o.ID DESC

The problem is, that even though I don't want "Alex Sorensen's" order, because it contains oranges, I get his other lines without the one containing "Orange".

I need an SQL-code to give me "Fred Jonsson"s and the other orders, that don't have oranges in the order.

Answer 1

Change

 p.Product NOT IN ('Orange')

to

k.Customer NOT IN (select DISTINCT Customer from Products where Product= 'Orange')


SELECT o.ID, k.Customer, o.Quantidy, p.Product, o.Date
FROM Products p, Orders o, Customers c
WHERE p.ID = o.ID
AND k.Customer = o.Customer
AND k.Customer NOT IN (select DISTINCT Customer from Products where Product= 'Orange')
GROUP BY o.Date
ORDER BY o.ID DESC

Answer 2

You should be able to use NOT EXISTS:

SELECT o.ID, c.Customer, o.Quantity, p.Product, o.Date
FROM Products p, Orders o, Customers c
WHERE p.product = o.product
AND c.Customer = o.Customer
AND NOT EXISTS ( SELECT 1 FROM Orders o2
                 WHERE o2.product = 'Orange'
                 AND o2.customer = o.customer
                 AND o2.date = o.date
               )

---

Try to use explicit join syntax by the way, it will make reading your queries easier once you are used to it:

SELECT o.ID, c.Customer, o.Quantity, p.Product, o.Date
FROM Orders o
JOIN Products p ON ( p.product = o.product )
JOIN Customers c ON ( c.Customer = o.Customer )
WHERE NOT EXISTS ( SELECT 1 FROM Orders o2
                   WHERE o2.product = 'Orange'
                   AND o2.customer = o.customer
                   AND o2.date = o.date
                 )

Another approach is to use a Left Join and check for NULL:

SELECT o.ID, c.Customer, o.Quantity, p.Product, o.Date
FROM Orders o
JOIN Products p ON ( p.product = o.product )
JOIN Customers c ON ( c.Customer = o.Customer )
LEFT JOIN Orders o2 ON (     o2.product = 'Orange'
                         AND o2.customer = o.customer
                         AND o2.date = o.date
                       )
WHERE o2.Id IS NULL

You will have to try which one performs better.