CODEWITHSUNDEEP
mysql
Sheap
Sheap

Reputation: 63

MySQL many-to-many junction table: Select all entries from A which contain no values in B not in list

I have a many-to-many relationship, joined through a junction table. My specific case is recipes and ingredients. I want to select all recipes which don't contain ingredients not in a given list. For example, if I input cheese, toast and crackers, I want the results to include cheese on toast, cheese with crackers, but not jam on toast.

So something like: 

SELECT * FROM
recipe
JOIN recipe_ingredient on recipe.id = recipe_ingredient.recipe_id
JOIN ingredient on ingredient.id = recipe_ingredient.ingredient_id
WHERE ingredient.name
???
("cheese", "toast", "crackers")

Selecting recipes which do contain any or all of these ingredients is easy enough, but if it can be avoided I don't want to have to then subsequently filter out results which contain unlisted ingredients.

Edit:
Some example tables:

ingredient
-----------
id | name
1  | "cheese"
2  | "toast"
3  | "crackers"
4  | "jam"

recipe
-----------
id | name
1  | "cheese on toast"
2  | "cheese with crackers"
3  | "jam on toast"

recipe_ingredient
-------------------------
recipe_id | ingredient_id
1         | 1
1         | 2
2         | 1
2         | 3
3         | 2
3         | 4

Upvotes: 2

Views: 294

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

One way to achieve this would be to select recipes that have any ingredient not listed in your criteria to match using ALL with a subquery:

SELECT r.id
FROM recipe r
JOIN recipe_ingredient ri on r.id = ri.recipe_id
JOIN ingredient i on i.id = ri.ingredient_id
WHERE i.name <> ALL ( SELECT 'cheese' UNION SELECT 'toast' UNION SELECT 'crackers' )
GROUP BY r.id

To retrieve only those recipees that match your conditions you could wrap the above statement using the very same <> ALL comparison.

SELECT *
FROM recipe
WHERE id <> ALL (
    SELECT r.id
    FROM recipe r
    JOIN recipe_ingredient ri on r.id = ri.recipe_id
    JOIN ingredient i on i.id = ri.ingredient_id
    WHERE i.name <> ALL ( SELECT 'cheese' UNION SELECT 'toast' UNION SELECT 'crackers' )
    GROUP BY r.id
    );

Additional note: Actually NOT IN is an alias for <> ALL, so you could use them interchangeably.

Given your sample it would only return:

id | name
---|-------------------------
1  | cheese on toast
2  | cheese with crackers

See it working here: http://sqlfiddle.com/#!9/f20010/25

Upvotes: 2

Jorge Campos
Jorge Campos

Reputation: 23361

Pretty trick this one. But it can be done. The trick part here is to know how much ingredients each recipe has and then compare it with amount of ingredients with the given parameters.

select tb.name
  from ( select r.id, r.name, count(*) qtd
           from ingredient i
                  inner join recipe_ingredient ri 
                     on i.id = ri.ingredient_id
                  inner join recipe r
                     on r.id = ri.recipe_id
           where i.name in ('cheese', 'toast', 'crackers')
           group by r.id, r.name
        ) tb
  where exists ( select 1
                   from ingredient i
                         inner join recipe_ingredient ri 
                            on i.id = ri.ingredient_id
                         inner join recipe rr
                            on rr.id = ri.recipe_id
                  where rr.id = tb.id
                  group by rr.id
                  having count(*) = tb.qtd)

First I selected all recipes that has those ingredients that you filter counting from it how much ingredients it has. That first query will give me:

"cheese on toast"        2
"cheese with crackers"   2
"jam on toast"           1

And on the EXISTS clause I made a subquery to count the total ingredients that all recipes have and joined with the upper subquery. So it will only give me the ones listed.

See it working here: http://sqlfiddle.com/#!9/f20010/22

Upvotes: 0

Related Questions