Search code examples
sqlpostgresqlrelational-division

Find a value which contains in ALL rows of a table

I need to select all values which are contained in ALL rows of a table.
I have table “Ingredient” and ProductIngredient(there I have a recipe of a product).
Ingredient 

| ingredient_id | name | price | 
| 1             | Bla  | 100   
| 2             | foo  | 50

ProductIngredient. 

| Product_id | ingredient_id
| 1          | 1   
| 1          | 2   
| 2          | 1

The output should be 

|  1   |  Bla |

as it is in all rows of ProductIngredient. 

SELECT DISTINCT Ingredient_Id 
FROM Ingredients I
WHERE Ingredient_Id = ALL
    (SELECT Ingredient_id FROM ProductIngredient PI
     WHERE PI.Ingredient_Id = I.Ingredient_Id );

How can I fix my code to make it work?

Solution

  • This will give you all Ingredients from I that are in PI for each product. This is assuming that each product does not have multiple rows for a product and ingredient combination. 

    SELECT I.Ingredient_Id 
FROM Ingredients I INNER JOIN ProductIngredient PI
     ON PI.Ingredient_Id = I.Ingredient_Id
GROUP BY I.Ingredient_Id
HAVING COUNT(*) >= (SELECT COUNT(DISTINCT Product_id) FROM ProductIngredient)