CODEWITHSUNDEEP
sqldatabasepostgresqlselect
Horse SMith
Horse SMith

Reputation: 1035

PostgreSQL - select only when specific multiple apperance in column

I'm using PostgreSQL. I have a table with 3 fields person, 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 which results in every person who whenever has added carrot to a recipe, the person must also have added salt to the same recipe.

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

If this the 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 this result: person1

Because this person is the only one who whenever has added carrot also have added salt.

"Nothing but the carrot could affect the result. I only want persons who has added at least one carrot in one of their recipes, but I don't want persons who have not also added salt to all of the same recipes they've added carrot to. Sorry, but I just can't explain it any clearer than that."

Upvotes: 4

Views: 211

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

This seems a variation of the relational division problem.

A 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 ;

Upvotes: 0

raina77ow
raina77ow

Reputation: 106385

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 I don't have much experience working with PostgreSql, but the query seems to give the results you require in this SQL Fiddle (credit to @JohnWoo for providing the one to begin from).

I have updated the answer; before it returned users with only salts in some of their recipes as legit ones. The second HAVING clause filters out such cases.

UPDATE: The previous query returned all the owners which have at least one recipe which follows the rule ("for each added carrot add salt also"). But you (seem to) actually need the ones with all the recipes following 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.

Upvotes: 1

user359040
user359040

Reputation:

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

Upvotes: 0

Related Questions