HElp :)

[Nickfalo]

Hello all,

I have a question.

If I have 1 table: Sells, like that:

Id_Client Id_Product
210 1
210 2
211 1
210 3
211 2
210 4
212 2
212 4

I want ONLY the client who buy the Id_Products 1,2 and 4.
How can I do that in a SQL Query?

Thanks!

[David Portas]

Assuming the key is (id_client, id_product) you can do this:

SELECT id_client
FROM SomeTable
WHERE id_product IN (1,2,4)
GROUP BY id_client
HAVING COUNT(*)=3

If (id_client, id_product) isn't unique:

SELECT id_client
FROM SomeTable
WHERE id_product IN (1,2,4)
GROUP BY id_client
HAVING COUNT(DISTINCT id_product)=3

--
David Portas
SQL Server MVP
--

[Bagieta]

What for you add HAVING COUNT???

[Hugo Kornelis]

On Fri, 18 Jun 2004 14:08:33 +0200, Bagieta wrote:

What for you add HAVING COUNT???

Hi Bagieta,

Without HAVING COUNT, the query would return all clients who buy at least
one of the products 1, 2, or 4. The HAVING COUNT ensures that only clients
are returned who buy all three products.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Portas]

My understanding was that the OP wanted clients who bought ALL THREE
products, that is 1, 2 and 4. Without HAVING COUNT(*)=3 my query would
return rows where the client bought just ANY ONE of those three.

--
David Portas
SQL Server MVP
--

[Bagieta]

Ok
I should have missed this 'and' word :)
Thanks