CODEWITHSUNDEEP
mysql
Krøllebølle
Krøllebølle

Reputation: 3028

MySQL: Exclude matches if criterion is met

I have a database with three tables: recipes, ingredients and allergens. There are many-to-many relationships between both recipes and ingredients, and ingredients and allergens. These relationships are solved using the intermediate tables ingredient_in_recipe and allergen_in_ingredient, which contain only the respective ids for recipes, ingredients and allergens.

Now, selecting all ingredients in a set of recipes is simple, for instance for the recipes with id = 1, 2, 3, 4 and 5 like this:

SELECT i.* FROM ingredients AS i
    INNER JOIN ingredient_in_recipe AS ir ON i.id = ir.ingredient_id
    INNER JOIN recipes AS r ON ir.recipe_id = r.id
WHERE r.id IN (1, 2, 3, 4, 5);

Furthermore, it is easy to add more INNER JOINs and WHERE clauses to remove certain ingredients from the resulting data set. However, when a recipe has an ingredient with a certain allergen, I want to remove all matched ingredients from the results for any recipe that has that allergen in it, since that recipe is no longer valid due to allergy. What is the best way to achieve this?

Upvotes: 1

Views: 75

Answers (2)

Krøllebølle
Krøllebølle

Reputation: 3028

Here is the solution I ended up with. I did not state it clearly in the question, but it is desirable to remove any recipe that has any ingredient that contains an allergen from a list. In the below, it is searched in all recipes and any recipe that contains allergen with id 1, 2 or 4. Since I did not need any data from the recipe table, it is left out of the last part of the query.

SELECT i.*, ir.*
FROM(SELECT ir.recipe_id
    FROM ingredient_in_recipe AS ir LEFT JOIN 
        allergen_in_ingredient AS ai ON ai.ingredient_id = ir.ingredient_id
    GROUP BY ir.recipe_id
    HAVING NOT MAX(ai.allergen_id IN (1, 2, 14))
    ) AS rsafe LEFT JOIN
        ingredient_in_recipe AS ir ON rsafe.recipe_id = ir.recipe_id INNER JOIN
        ingredients AS i on i.id = ir.ingredient_id
ORDER BY i.id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

The following query gets "safe" recipes with no allergens:

SELECT ir.recipe_id
FROM ingredient_in_recipe ir LEFT JOIN
     allergens_in_ingredient ai
     ON ai.ingredient_id = i.id
WHERE ir.recipe_id IN (1, 2, 3, 4, 5)
GROUP BY ir.recipe_id
HAVING MAX(ai.allergen_id IS NULL) IS NULL;

Note that you don't need the recipes table or the ingredients table to get the "safe" recipes.

You can then join this back to the ingredients to get safe recipes with their incredients:

SELECT i.*
FROM (SELECT ir.recipe_id
      FROM ingredient_in_recipe ir LEFT JOIN
           allergens_in_ingredient ai
           ON ai.ingredient_id = i.id
      WHERE ir.recipe_id IN (1, 2, 3, 4, 5)
      GROUP BY ir.recipe_id
      HAVING MAX(ai.allergen_id IS NULL) IS NULL
     ) rsafe JOIN
     ingredient_in_recipe ir
     ON rsafe.recipe_id = ir.recipe_id JOIN
     ingredients i INNER JOIN
     ON i.id = ir.ingredient_id
ORDER BY i.recipe_id;

Upvotes: 1

Related Questions