CODEWITHSUNDEEP
sqlsql-serverselectrow-numbersequential-number
angelo
angelo

Reputation: 5

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

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

Upvotes: 0

Views: 26

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

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

Upvotes: 1

Related Questions