Posted by Ed Murphy on 04/18/07 02:03

tizmagik@gmail.com wrote:
> On Apr 17, 9:43 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
>> tizma...@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.
>
> Thanks Ed, but I'm trying to avoid using COUNT since that was not
> covered in class.

SELECT C1.Customer_Name
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
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
)

or

SELECT C1.Customer_Name
FROM Customer C1
WHERE NOT EXISTS (
SELECT P.ItemID
FROM Customer C2
JOIN Prefer P ON C2.CustomerID = P.CustomerID
WHERE C2.Customer_Name = 'John'
AND P.ItemID NOT IN (
SELECT B.ItemID
FROM Bought B
WHERE B.CustomerID = C1.CustomerID
)
)

[Back to original message]