Posted by Erland Sommarskog on 04/14/07 10:49

(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#

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]