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

Reputation: 3028

MySQL: Exclude subsets if not all row values are true

I have three MySQL tables: ingredients, ingredient_in_recipe and recipes which can be INNER JOINed to get ingredients in recipes. Also, the ingredients table has a column vegetarian. I want to get all recipes that are considered vegetarian, meaning that all ingredients for a given recipe must have set vegetarian to 1 (it is a BOOL/tinyint(1)).

I have looked at queries using ALL, HAVING NOT MAX and other various stuff, but I cannot find a working solution. What is the best way to do this? Are there some solutions that are more efficient than others?

Extra (only relevant) table information:

mysql> DESCRIBE ingredients;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| name            | varchar(100)  | NO   |     | NULL    |                |
| vegetarian      | tinyint(1)    | NO   |     | 0       |                |
+-----------------+---------------+------+-----+---------+----------------+

mysql> DESCRIBE ingredient_in_recipe;
+---------------+------------------------+------+-----+---------+-------+
| Field         | Type                   | Null | Key | Default | Extra |
+---------------+------------------------+------+-----+---------+-------+
| recipe_id     | int(11)                | NO   |     | NULL    |       |
| ingredient_id | int(11)                | NO   |     | NULL    |       |
+---------------+------------------------+------+-----+---------+-------+

mysql> DESCRIBE recipes;
+------------------------+----------------------+------+-----+---------+----------------+
| Field                  | Type                 | Null | Key | Default | Extra          |
+------------------------+----------------------+------+-----+---------+----------------+
| id                     | int(11)              | NO   | PRI | NULL    | auto_increment |
| name                   | text                 | NO   |     | NULL    |                |
+------------------------+----------------------+------+-----+---------+----------------+

The start of my query is currently:

SELECT recipe.name, ingredient.name
    FROM ingredients AS ingredient
    INNER JOIN ingredient_in_recipe AS ir
        ON ir.ingredient_id = ingredient.id
    INNER JOIN recipes AS recipe
        ON ir.recipe_id = recipe.id;

So I am missing a WHERE, ALL, IN or something statement at the end.

Upvotes: 0

Views: 75

Answers (2)

Bret Weinraub
Bret Weinraub

Reputation: 2283

Think of it this way.

  1. Select out the set of recipes that have any non-vegitarian ingredients.
  2. Subtract this set from the set of all recipes.

So here's the set of all recipes with a non veg ingredient.

    select
      id 
    from
      recipes,
      ingredient_in_recipe,
      ingredients
    where
      ingredient_in_recipe.recipe_id = recipes.id
    and
      ingredient_in_recipe.ingredient_id = ingredients.id
    and
      ingredients.vegetarian <> 1

Note: why are you using a tinyint to mark a boolen? USE Boolean to mark boolean.

Also your DB model is pretty good. Your naming is consistent and appropriate.

Now that we have the "non-vegitarian" recipes, we just subtract from a "set" perspective [as in set theory].

select
  *
from
  recipes
where
  id NOT IN (
    -- this subquery returns a set of IDs corresponding to non-vegitarian recipes.
    select
      id 
    from
      recipes,
      ingredient_in_recipe,
      ingredients
    where
      ingredient_in_recipe.recipe_id = recipes.id
    and
      ingredient_in_recipe.ingredient_id = ingredients.id
    and
      ingredients.vegetarian <> 1
  );

Upvotes: 1

JuniorCompressor
JuniorCompressor

Reputation: 20025

You can try the following:

SELECT r.name FROM recipes r WHERE r.id NOT IN (
  SELECT ir.recipe_id FROM ingredient_in_recipe ir
  INNER JOIN ingredients i ON ir.ingredient_id = i.id
  WHERE i.vegeterian = 0
)

Upvotes: 1

Related Questions