ChupChapCharli ChupChapCharli - 3 months ago 8
SQL Question

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

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

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