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
SELECT p.*
FROM product p
INNER JOIN (
(
SELECT product
FROM Ingreds
GROUP BY product
HAVING COUNT(0) >= 3
) g ON g.product = p.product