create table #Recipe (RecipeID smallint identity, RecipeName varchar(10))
insert #Recipe values('Recipe1')
insert #Recipe values('Recipe2')
insert #Recipe values('Recipe3')
create table #Ingredients (IngredientID smallint identity, Ingredient_Name varchar(10))
insert #Ingredients values('I1')
insert #Ingredients values('O1')
insert #Ingredients values('I2')
insert #Ingredients values('J2')
insert #Ingredients values('K1')
create table #Recipe_Ingredients (PKID smallint identity, IngredientID smallint, RecipeID smallint)
insert #Recipe_Ingredients values(1, 1)
insert #Recipe_Ingredients values(2, 1)
insert #Recipe_Ingredients values(3, 1)
insert #Recipe_Ingredients values(4, 2)
insert #Recipe_Ingredients values(5, 2)
insert #Recipe_Ingredients values(2, 3)
insert #Recipe_Ingredients values(5, 3)
go
Print 'All recipes & ingredients...'
select r.RecipeName, ingredient_name
from #Recipe r
join #Recipe_Ingredients ri on r.recipeid = ri.recipeid
join #ingredients i on ri.ingredientid = i.ingredientid
go
Print 'With 1 ingredient...'
declare @i1 varchar(10), @i2 varchar(10), @i3 varchar(10)
select @i1 = 'k1', @i2 = '', @i3 = ''
select r.RecipeName
from #Recipe r
join #Recipe_Ingredients ri on r.recipeid = ri.recipeid
join #ingredients i on ri.ingredientid = i.ingredientid
group by r.RecipeName
having sum(case when i.ingredient_name not in (@i1, @i2, @i3) then 1 else 0 end) = 0
go
print 'With 3 ingredients (out of which 2 can be used for a recipe) that match 2 recipes...'
declare @i1 varchar(10), @i2 varchar(10), @i3 varchar(10)
select @i1 = 'k1', @i2 = 'o1', @i3 = 'j2'
select r.RecipeName
from #Recipe r
join #Recipe_Ingredients ri on r.recipeid = ri.recipeid
join #ingredients i on ri.ingredientid = i.ingredientid
group by r.RecipeName
having sum(case when i.ingredient_name not in (@i1, @i2, @i3) then 1 else 0 end) = 0
go
print 'With 3 ingredients matching only one recipe...'
declare @i1 varchar(10), @i2 varchar(10), @i3 varchar(10)
select @i1 = 'i1', @i2 = 'o1', @i3 = 'i2'
select r.RecipeName
from #Recipe r
join #Recipe_Ingredients ri on r.recipeid = ri.recipeid
join #ingredients i on ri.ingredientid = i.ingredientid
group by r.RecipeName
having sum(case when i.ingredient_name not in (@i1, @i2, @i3) then 1 else 0 end) = 0

This page was last updated on May 01, 2006 04:28 PM.