0

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

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
angelo
  • 5
  • 2

1 Answers1

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
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794