On 18 Aug 2005 08:27:30 -0700, Tim wrote:
hi all,
I have a table of customers.
I have a table of products they have ordered.
How can I find all customers who have ordered productA and productB at
any time.
It sounds so easy, but I can't quite get my head around it!
thanks
Tim
Hi Tim,
Here's a version that's both shorter and more expandable than Jim's
suggestion:
SELECT Customer
FROM Orders
WHERE Product IN ('A', 'B')
GROUP BY Customer
HAVING COUNT(*) = 2
Easy to expand to three, four, ... products. And if you ahve the
products you want to search for in a seperate table, the query changes
to one of the following two (they are equivalent, but might have
different execution plans - test both if speed matters)
SELECT Customer
FROM Orders
WHERE Product IN (SELECT Product FROM ProductsToFind)
GROUP BY Customer
HAVING COUNT(*) = (SELECT COUNT(*) FROM ProductsToFind)
SELECT Customer
FROM Orders
WHERE EXISTS
(SELECT *
FROM ProductsToFind
WHERE ProductsToFind.Product = Orders.Product)
GROUP BY Customer
HAVING COUNT(*) = (SELECT COUNT(*) FROM ProductsToFind)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)