should be easy query question

[Tim]

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

[Jim]

In article <11************ **********@g44g 2000cwa.googleg roups.com>, 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.

Do you mean productA AND productB, or productA OR productB?

In other words, do you want clients who have ordered both products, or
all customer who have ordered either product?

One's easy, the other is...less so.

Jim
--
Find me at http://www.ursaMinorBeta.co.uk
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.

[Tim]

not surprisingly, it's the difficult option I'm after where I want to
return all customers who have bought productA and productB.

thanks

[Jim]

In article <11************ **********@z14g 2000cwz.googleg roups.com>, Tim wrote:

not surprisingly, it's the difficult option I'm after where I want to
return all customers who have bought productA and productB.

Try something like:

select custid from table where product='Produc tA' and custid in
(select custid from table where product='Produc tB') order by custid

Can't promise it will work but a few tests of the results should prove
it one way or the other.

Jim
--
Find me at http://www.ursaMinorBeta.co.uk
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.

[Stu]

Without your table structures, this is only a guess;

SELECT *
FROM Customers
WHERE CustomerID IN (SELECT a.CustomerID
FROM (SELECT *
FROM products
WHERE p.Type = 'A') a
JOIN
(SELECT *
FROM products
WHERE p.Type = 'B') b
ON a.CustomerID = b.CustomerID)

is one way to do it.

Stu

[Tim]

yes I can see that would work. thanks.
but just for fun, how would that look if I wanted to compare 3, 4 or
even 10 products?!
does it just turn into a nasty mess?

I was half wondering whether to run a script now and then to flatten
the tables into a new table to string all products purchased into 1
field, and use the instring function to find what I'm looking for.

[Hugo Kornelis]

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)

[Stu]

Hugo,

That's awesome. I knew there had to be a better way to do that than my
lousy suggestion, and that's really clean.

Stu