To find records which are contains all comma-separated list of Id's SQL Server

Question

It have 2 tables like this:

t_recipe:

RecipeId        Name              InsertDate 
----------------------------------------------
1             Mutton            9/6/2015 0:00
2             Veg Biryani       9/5/2015 0:00

t_recipe_ingredient:

RecipeId      IngrId          InsertDate
----------------------------------------------
1             200               9/6/2015 0:00
1             201               9/5/2015 0:00
1             101               9/4/2015 0:00
1             103               9/3/2015 0:00
2             100               9/2/2015 0:00
2             500               9/6/2015 0:00
2             202               9/5/2015 0:00
2             200               9/4/2015 0:00

Now when I am using below query:

select *
from t_recipe r
    join t_recipe_ingredient i ON r.RecipeID = i.RecipeId
where i.IngrId in (200, 201)

I am getting both the recipes in output however it should give me only Mutton as it is the one which contains both the ingredients. It seems like my query is checking at least one match however I want that it should return only those recipes which contains all the ingredients in in clause.

Answer 1

You need to group by your recipe and take only those groups having both ingredients

select r.RecipeId, r.Name, r.InsertDate  
from t_recipe r 
join t_recipe_ingredient i ON r.RecipeID = i.RecipeId 
where i.IngrId in (200,201)
group by r.RecipeId, r.Name, r.InsertDate
having count(distinct i.IngrId) = 2