CODEWITHSUNDEEP
mysqlsql
Sofia
Sofia

Reputation: 33

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!

Upvotes: 2

Views: 433

Answers (4)

MysticForce
MysticForce

Reputation: 1311

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.

Upvotes: 0

Joe C
Joe C

Reputation: 3993

This works for Sql Server. I am sure there is an equivalent in MySql

Drop Table #Test1
Create Table #Test1 (Recipe Varchar(8000), Ingredient Varchar(8000))

Insert #Test1 Values ('sandwich', 'bread')
Insert #Test1 Values ('sandwich', 'lettuce')
Insert #Test1 Values ('sandwich', 'mustard')
Insert #Test1 Values ('sandwich', 'bacon')
Insert #Test1 Values ('sandwich', 'tomato')
Insert #Test1 Values ('bacon salad ', 'lettuce')
Insert #Test1 Values ('bacon salad ', 'tomato')
Insert #Test1 Values ('bacon salad', 'bacon')
Insert #Test1 Values ('veggie salad', 'lettuce')
Insert #Test1 Values ('veggie salad', 'tomato')
Insert #Test1 Values ('veggie salad', 'cucumber')

;With cteQuery As
(
Select T.*, A.Ingredient IngredientMatch
    From #Test1 T
    Left Join (
        Select Ingredient 
            From #Test1
            Where Recipe = 'Sandwich'
        ) A On A.Ingredient = T.Ingredient
    Where Recipe != 'Sandwich'
)
Select Distinct Recipe From cteQuery Where Recipe Not In 
    (Select Recipe From cteQuery Where IngredientMatch Is Null)

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

Could be you need an inner join 

select distinct a.Recipe 
from my_table as a
inner my_table as b on a.ingredient = b.ingredient;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want to count the number of ingredients that are the same. You can do this using a left join and aggregation:

select i.recipe
from ingredients i left join
     ingredients i2
     on i.ingredient = i2.ingredient and i2.recipe = 'sandwich' and
        i.recipe <> i2.recipe
group by i.recipe
having count(*) = count(i2.ingredient);

The having clause checks that all ingredients have a match for sandwich.

Upvotes: 2

Related Questions