Posted by tizmagik on 04/18/07 01:55

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.

[Back to original message]