Posted by Ed Murphy on 04/16/07 03:32

Erland Sommarskog wrote:

> (tizmagik@gmail.com) writes:
>> Database consists of the following 4 tables with respective
>> attributes:
>>
>> CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]
>> ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]
>> BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]
>> PREFER(I#, C#), the key is [I#, C#]
>>
>> I'm trying to construct the following query (in SQL)
>>
>> List of customers that bought all the items that John prefers.
>>
>> I can get the list of all the items that John prefers, but I'm not
>> sure how to check that list against customers who bought ALL those
>> items. I'm assuming it's either a division or some sort of subtraction
>> but I'm not sure how to formulate the SQL query.
>
> This smells of class assignment, but OK, let's go for it anyway.
>
> If memory serves this is something they for some reason I've never
> understood call relational division. In less occluded terms, a HAVING
> clause can shortcut the need for a couple of EXISTS and NOT EXISTS.
>
> SELKCT C.C#, C.CUSTOMER_NAME
> FROM CUSTOMER C
> JOIN (SELECT B.C#
> FROM BOUGHT B
> GROUP BY B.C#
> HAVING COUNT(DISTINCT B.I#) =
> (SELECT COUNT(*)
> FROM PREFER P
> JOIN CUSTOMER C ON P.C# = C.C#
> WHERE C.CUSTOMER_NAME = 'John')) AS res
> ON C.C# = res.C#

That will select all customers who bought the same /number/ of
items as what John prefers, but not necessarily the same items.

I think this will select all customers who bought all the items
that John prefers:

SELECT C.C#, C.CUSTOMER_NAME
FROM CUSTOMER C
JOIN BOUGHT B ON C.C# = B.C#
JOIN PREFER P ON B.I# = P.I#
JOIN CUSTOMER J ON P.C# = J.C# AND J.CUSTOMER_NAME = 'John'
GROUP BY C.C#, C.CUSTOMER_NAME
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM PREFER P
JOIN CUSTOMER J ON P.C# = J.C# AND J.CUSTOMER_NAME = 'John'
)

[Back to original message]