is there a way to select only records with ingredient numbers of 3 or more

Question

I have a table of compound ingredients. depending on the product each product may have more than one ingredient number:

Product	Ingred No
A	1
B	1
B	2
C	1
C	2
C	3
D	1
D	2
D	3
D	4

I only want to select products with 3 or more ingredients so the result would be:

Product	IngredNo
C	1
C	2
C	3
D	1
D	2
D	3
D	4

Hope this makes sense!

i did try Row_Number partition by product, but i cannot get it to work.

i tried the following and it works by itself:

( SELECT product FROM Ingreds 
       GROUP BY product HAVING COUNT(0) > 3 ) y
        ON y.product = x.product))

But when i use it in a where clause :

select product,ingredno
from Ingreds
where exists ( SELECT product FROM Ingreds 
       GROUP BY product HAVING COUNT(0) > 3 ) y
        ON y.product = x.product))

i still get all the records, not the one with more than 3 ingredients

Answer 1

SELECT p.*
FROM product p
INNER JOIN (
( 
   SELECT product 
   FROM Ingreds 
   GROUP BY product 
   HAVING COUNT(0) >= 3 
) g ON g.product = p.product