CODEWITHSUNDEEP
sqldatabase
dimalc
dimalc

Reputation: 23

Getting the recipe only if the all ingredients match

I have the tables :

create table recipe(
recipe_id int IDENTITY(1,1) primary key,
recipe_name varchar(100),
decription text);

create table ingredient(
ingredient_id int IDENTITY(1,1) primary key,
ingredient_name varchar(100))

create table recipe_i(
id_h int identity(1,1) primary key,
rec_id int foreign key references recipe(recipe_id),
ing_id int foreign key references ingredient(ingredient_id))

I get various number of ingredients from user,for example ('milk','flour','salt','egg',...) I want to return a recipe only if all these ingredients are in the recipe.

I have tried :

select recipe_name
from recipe
where recipe_id  =
    ( select distinct rec_id
        from recipe_i 
       where ing_id =all
              ( select ingredient_id
                  from ingredient
                  where ingredient_name in ('flour','egg','oil','salt','milk')
              )
    );

,but the result is empty.

Upvotes: 2

Views: 618

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

You can match the counts. This is one way. It will match all recipes that can be made using the given ingredients, but you may have ingredients left. For instance, this will match recipes with just flour and milk, but not recipes with flour and yeast, because yeast is not specified.

select recipe_name
from recipe r
where 
  -- Where total number of ingredients ...
  ( select count(*) from recipe_i ri 
    where ri.rec_id = r.recipe_id) 
    = -- equals
  -- specified ingredients
  ( select count(*) 
    from recipe_i ri 
    inner join ingredient i on i.ingredient_id = ri.ing_id
    where 
      ri.rec_id = r.recipe_id and
      i.ingredient_name in ('flour','egg','oil','salt','milk'));

But reading it again, I think you want it the other way around, and only return recipes that contain all ingredients. The following query does that, but will also return recipes that contain these ingredients and more.

select recipe_name
from recipe r
where 
  -- Total number of ingredients
  ( select count(*) 
    from ingredient i
    where 
      i.ingredient_name in ('flour','egg','oil','salt','milk'))
    = -- equals
  -- number of matching ingredients in the recipe
  ( select count(*) 
    from recipe_i ri 
    inner join ingredient i on i.ingredient_id = ri.ing_id
    where 
      ri.rec_id = r.recipe_id and
      i.ingredient_name in ('flour','egg','oil','salt','milk'));

You could combined the two conditions (with and) in one query to get the recipes that match exactly the given ingredients, if you like.

Upvotes: 1

Related Questions