Get recipes which don't include X ingredients

Question

I have tree tables:

recipes:

id | title | ....

recipe_ingredients:

recipe_id | ingredient_id

ingredients:

id | ingredient

Each recipe has n of ingredients associated to it.

I wrote a query which gets recipe id, ingredients match count, total number of ingredients for that recipe by ingredient IDs:

SELECT recipes.id, recipes.title, ing_match_count,
    (
    SELECT count(id)
    FROM recipe_ingredients as ri
    WHERE ri.recipe_id = recipes.id
    ) as recipe_ing_count
FROM recipes
RIGHT JOIN (
    SELECT recipe_id, ingredients_id, COUNT(*) AS ing_match_count
    FROM recipe_ingredients
    WHERE ingredients_id IN (19, 25, 30, 40)
    GROUP BY recipe_id
) AS ri
ON recipes.id = ri.recipe_id
ORDER BY ing_match_count DESC

The problem is, I'am unable to exclude recipes using other ingredient IDs. The query above search for recipes which have 19, 25, 30, 40 ingredient IDs. But I want to exclude recipes for example which have 22, 23 ingredients as well.

So if recipe has 19 and 22 ingredient id, it won't be displayed.

Gordon Linoff Gordon Linoff · Accepted Answer · 2014-04-30T13:15:04

I am a fan of using group by with the conditions in the having clause for these types of queries. I find that this is the most flexible appraoch.

SELECT r.id, r.title,
       SUM(ingredients_id IN (19, 25, 30, 40)) as ing_match_count,
       COUNT(*) as recipe_ing_count
FROM recipes r JOIN
     recipe_ingredients  ri
     ON ri.recipe_id = recipes.id
GROUP BY r.id, r.title
HAVING ing_match_count > 0 AND
       SUM(ingredients_id in (22, 23)) = 0;

Get recipes which don't include X ingredients

1 Answers