Posted by Ed Murphy on 04/18/07 01:43

tizmagik@gmail.com wrote:

> Okay, this is what I have come up with so far:
>
> SELECT c1.Customer_Name
> FROM Customer c1
> WHERE c1.CustomerID IN (
> SELECT B.CustomerID
> FROM Bought B
> WHERE B.ItemID IN (
> SELECT P.ItemID
> FROM Prefer P, Customer c2
> WHERE c2.Customer_Name = 'John'
> AND c2.CustomerID = P.CustomerID ) )
>
> But that brings me back to the problem where it will list customers
> that bought *at least one* of the items that John prefers, not ALL of
> the items that John prefers, that query gives:

I think this will work:

SELECT c1.Customer_Name
FROM Customer c1
WHERE 0 = (
SELECT COUNT(*)
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
LEFT JOIN Bought B ON P.ItemID = B.ItemID
AND B.CustomerID = C1.CustomerID
WHERE C2.Customer_Name = 'John'
AND B.CustomerID IS NULL
)

but I still think the positive approach (COUNT = COUNT) is a lot
easier to understand.

[Back to original message]