MYSQL – Fetching entities whose many-to-many relationship contains all terms collectively

I am using the following DB Schema

create table Recipe (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(25), 
    description VARCHAR(50), 
    instructions VARCHAR(500)) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table Ingredient (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(50)) 

create table Measure (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(30)) 

create table RecipeIngredient (recipe_id INT NOT NULL, 
    ingredient_id INT NOT NULL, 
    measure_id INT, 
    amount INT, 
    CONSTRAINT fk_recipe FOREIGN KEY(recipe_id) REFERENCES Recipe(id), 
    CONSTRAINT fk_ingredient FOREIGN KEY(ingredient_id) REFERENCES Ingredient(id), 
    CONSTRAINT fk_measure FOREIGN KEY(measure_id) REFERENCES Measure(id))

and using something like this to add data/query it

INSERT INTO Measure (name) VALUES('CUP'), ('TEASPOON'), ('TABLESPOON');

INSERT INTO Ingredient (name) VALUES('egg'), ('salt'), ('sugar'), ('chocolate'), ('vanilla extract'), ('flour');

INSERT INTO Recipe (name, description, instructions) VALUES('Chocolate Cake', 'Yummy cake', 'Add eggs, flour, chocolate to pan. Bake at 350 for 1 hour');


INSERT INTO RecipeIngredient (recipe_id, ingredient_id, measure_id, amount)  VALUES (1, 1, NULL, 3);

INSERT INTO RecipeIngredient (recipe_id, ingredient_id, measure_id, amount)  VALUES (1, 2, 2, 1);

INSERT INTO RecipeIngredient (recipe_id, ingredient_id, measure_id, amount)  VALUES (1, 3, 1, 2);

INSERT INTO RecipeIngredient (recipe_id, ingredient_id, measure_id, amount)  VALUES (1, 4, 1, 1);

SELECT r.name AS 'Recipe', 
    ri.amount AS 'Amount', 
    mu.name AS 'Unit', 
    i.name AS 'Ingredient' 
FROM Recipe r 
JOIN RecipeIngredient ri on r.id = ri.recipe_id 
JOIN Ingredient i on i.id = ri.ingredient_id 
LEFT OUTER JOIN Measure mu on mu.id = measure_id;

which returns

Recipe              | Amount    | Unit     | ingredient
Chocolate Cake      | 3         |null      | egg
Chocolate Cake      | 1         |TEASPOON  | salt
Chocolate Cake      | 2         |CUP       | sugar
Chocolate Cake      | 1         |CU        | chocolate

How can I return recipes who’s ingredients are a subset of a list of ingredients provided?

e.g I should not return Chocolate cake if list (egg, salt, sugar) is provided. However I should return Chocolate cake if (egg, salt, ham, cheese, sugar, sausage, chocolate) is provided.

Answer

You can use aggregation and a having clause:

SELECT r.name recipe
FROM Recipe r 
JOIN RecipeIngredient ri on r.id = ri.recipe_id 
JOIN Ingredient i on i.id = ri.ingredient_id 
GROUP BY r.id, r.name
HAVING MAX(i.name NOT IN ('egg', 'salt', 'sugar')) = 0

You control the elements the the ingredients should be a subset of with the list of values in the right operand to the IN conditions.

Advertisement

Answer