PostgreSQL - select only if a certain number of instances are specified in the column

Question

I am using PostgreSQL. I have a table with three fields man, recipe and ingredient

person = creator of the recipe recipe = the recipe ingredient = one of the ingredients in the recipe 

I want to create a query as a result of which every person who has ever added carrots to a recipe should also add salt to the same recipe.

More than one person can create a recipe, in which case the person who added the ingredient will be credited for adding the ingredient. Sometimes an ingredient is used more than once, even by the same person.

If this is a table:

 person1, rec1, carrot person1, rec1, salt person1, rec1, salt person1, rec2, salt person1, rec2, pepper person2, rec1, carrot person2, rec1, salt person2, rec2, carrot person2, rec2, pepper person3, rec1, sugar person3, rec1, carrot 

Then I want to get this result: face1

Because this person is the only one who added salt when adding carrots.

“Nothing but carrots can affect the result. I only need people who have added at least one carrot in one of their recipes, but I don’t want people who also did not add salt to all the same recipes that they are “Added carrots." Sorry, but I just can't explain it more clearly than that. "

Answer 1

How about this:

  SELECT DISTINCT person FROM tableName WHERE ingredient IN('carrot', 'salt') GROUP BY person, recipe HAVING SUM(CASE WHEN ingredient = 'carrot' THEN 1 ELSE -1 END) <= 0 AND COUNT(DISTINCT ingredient) > 1; 

I admit that I don't have much experience with PostgreSql, but the query seems to give the results you need in SQL Fiddle (credit for @JohnWoo to ensure you start with).

I updated the answer; before he returned to users only salts in some of his recipes as legal. The second HAVING filters out such cases.

UPDATE: the previous query returned to all owners who have at least one recipe that follows the rule ("for each added salt with carrots too"). But you (it seems) really need those that have all the recipes that follow the rule. So the query looks like ...

 SELECT DISTINCT person FROM tableName WHERE person NOT IN ( SELECT person FROM tableName WHERE ingredient IN('carrot', 'salt') GROUP BY person, recipe HAVING SUM(CASE WHEN ingredient = 'carrot' THEN 1 ELSE -1 END) > 0 ); 

SQL Fiddle to play with.

Answer 2

Try:

 SELECT person from (SELECT person, recipe, COUNT(DISTINCT ingredient) ingredients FROM tableName WHERE ingredient IN ('salt', 'carrot') GROUP BY person, recipe HAVING MAX(CASE WHEN ingredient = 'carrot' THEN 1 END) = 1) p group by person HAVING MIN(ingredients) = 2 

Answer 3

This seems like a variation on the problem of relational division.

Double Nested NOT EXISTS Solution:

 SELECT DISTINCT person FROM tableName AS t WHERE NOT EXISTS ( SELECT * FROM tableName AS chosen WHERE chosen.ingredient = 'carrot' AND chosen.person = t.person AND NOT EXISTS ( SELECT * FROM tableName AS required WHERE required.ingredient = 'salt' AND required.recipe = chosen.recipe AND required.person = chosen.person ) ) ; 

And a JOIN :

 SELECT DISTINCT t.person FROM tableName AS t LEFT JOIN tableName AS chosen LEFT JOIN tableName AS required ON required.ingredient = 'salt' AND required.recipe = chosen.recipe AND required.person = chosen.person ON chosen.ingredient = 'carrot' AND chosen.person = t.person AND required.ingredient IS NULL WHERE chosen.ingredient IS NULL ;