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

Question

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.

This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. — philipxy, Oct 12 '20 at 12:57
Does this answer your question? [Select values that meet different conditions on different rows?](https://stackoverflow.com/questions/477006/select-values-that-meet-different-conditions-on-different-rows) — philipxy, Oct 13 '20 at 01:23

GMB · Accepted Answer · 2020-10-12T21:34:57.360

1

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.

edited Oct 12 '20 at 21:34

answered Oct 12 '20 at 12:53

GMB

216,147
25
84
135

. . Actually, the `IN` is not needed. If the joins produce any rows, then the `NOT IN` guarantees that they are the matching ingredients. – Gordon Linoff Oct 12 '20 at 13:19
@GordonLinoff: ah yes, that makes sense. I actually started the answer without `IN`, and then added it; but indeed that's not necessary. Thanks. – GMB Oct 12 '20 at 21:35

score 0 · Answer 2 · answered Oct 12 '20 at 21:17

Hints

SELECT ingredient AS what_you_are_missing
    FROM  RecipeIngredient
    WHERE Recipe = 'Chocolate cake'
      AND NOT FIND_IN_SET(ingredient, "egg,salt,sugar") )

Or... If the list of possible ingredients is small enough, then a SET or BIGINT UNSIGNED could be used for ANDs and ORs, etc to manipulate "set" operations.

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

2 Answers2