Posted by tizmagik on 04/18/07 02:13

On Apr 17, 10:03 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> tizma...@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
> )
> )

That first one didn't work for me, some syntax error, not sure why,
might just be a phpMyAdmin problem, but that second one worked
beautifully.

I'm trying to step through it and understand it line by line now...
this is what I understand from it:

You are selecting all the customers that are not in the following:
- You are selecting all the Items that john prefers, from the list of
items that are not in the list of items that customers have bought

haha really confusing, but I think I get it. Thanks so much for your
help.

[Back to original message]