Sofia Sofia - 5 months ago 10
MySQL Question

SQL: A query that returns items that have a subset of values from a given item

I'm wondering if it's possible to do the something with a single mysql query and if so, then curious about your thoughts on the most efficient way of doing it.

I have a table with two fields...let's say recipe and ingredient

I'm looking for all recipes that have a subset of ingredients of a given recipe.

To illustrate that:

Recipe | Ingredient
-------------------
sandwich | bread

sandwich | lettuce

sandwich | mustard

sandwich | bacon

sandwich | tomato

bacon salad | lettuce

bacon salad | tomato

bacon salad | bacon

veggie salad | lettuce

veggie salad | tomato

veggie salad | cucumber


I'm passing in "sandwich" as a parameter and need a query that will return the bacon salad (i.e. the ingredients are all included in the list of sandwich ingredients), but not the veggie salad, as it contains cucumber, which is not in the sandwich.

I reviewed this Q:

SQL query to exclude records that appear in other rows?

but I think that my case is different and more complicated. It would be similar if I was passing in "bacon" and excluding all recipes that contain ingredients other than "bacon", but I need to generate the list of what I'm excluding based on a query of the input recipe ingredients.

Hope that makes sense! I feel like this should be fairly trivial but am stuck.

TIA for you help!

Answer

lets say table name is kitchen

select * from kitchen k0 where Recipe NOT IN 
(
select * from kitchen k1 where Recipe!="sandwitch" and 
ingredient NOT IN (select k2.ingredient from kitchen k2 where Recipe="sandwitch"))

Explanation:

  • Find the recipes which have atleast one ingredient that is not an ingredient of "sandwitch"
  • Now find the recipes in the table which are not in the above set of recipes.
Comments