How do I extend this query to find valid combinations of three items?

I totally don't expect to get any answers here, but I'll try anyway.

So this came out of playing Skyrim. I wanted an easy way to look up what ingredients can be combined to make different potions/poisons so I made an Ingredient table that has an ID and a Name; an Effect table that has an ID, Name, Poison flag, and Potion flag (potion and poison are mutually exclusive); and a join table that has ID for ingredient and ID for effect.

So the way it works is every ingredient has 4 different effects, effects are repeated on mulitple ingredients. In the game you can combine 2 or 3 ingredients and the result is a potion or poison with all of the effects that are matching on at least 2 of the ingredients used. So if you use 3 ingredients and effect1 is on both ingredient1 and ingredient2 and effect2 is on both ingredient1 and ingredient3 your result will be a potion/poison that has both effect1 and effect2.

I was able to come up with a query on my own that will show every possible 2 ingredient combination that creates a potion with no poison effects. First I need to find every possible 2 ingredient combination that only has matching effects that are not "poison":

SELECT i1.UniqIngredient UniqIngredient1, i2.UniqIngredient UniqIngredient2
FROM Ingredient i1
CROSS JOIN Ingredient i2
INNER JOIN IngredientEffectJT jt1 ON i1.UniqIngredient = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON i2.UniqIngredient = jt2.UniqIngredient
INNER JOIN Effect e ON jt1.UniqEffect = e.UniqEffect AND jt2.UniqEffect = e.UniqEffect
WHERE i1.UniqIngredient < i2.UniqIngredient
GROUP BY i1.UniqIngredient, i2.UniqIngredient
HAVING SUM(e.Poison) = 0

Ingredient is cross joined with Ingredient to get every combination but because the order of the ingredients doesn't matter, I'd end up with double the results. That's why the WHERE checks i1.UniqIngredient < i2.UniqIngredient. I will only ever see each combination once and the lower

sql t-sql

Edit Report

1 Answer

Try this

declare @combos table (comboId int identity, ingredient1 int, ingredient2 int, ingredient3 int null)

--create table of all unique 2 and 3 ingredient combinations (unique potions)
insert int @combos (ingredient1, ingredient2, ingredient3)
select 
    distinct
    i1.ID,
    i2.ID,
    i3.ID
from
    ingredient i1
    inner join ingredient i2 on i1.ID < i2.ID
    left outer join ingredient i3 on i2.ID < i3.ID

--create table to hold mapping between unique combinations and ingredients
declare @combo_ingredient table (ComboId int, IngredientId int)

--insert into the mapping table
insert into @combo_ingredient (ComboId, IngredientId)
select ID, ingredient1 from @combos

insert into @combo_ingredient (ComboId, IngredientId)
select ID, ingredient1 from @combos

insert into @combo_ingredient (ComboId, IngredientId)
select ID, ingredient3 from @combos where ingredient3 is not null

--create table to hold mapping between unique combinations (potions) and the effects it will have
declare @combo_effect (comboId int, effectId int)

insert into @combo_effect (comboId, effectId)
select 
    c.ComboId, ec.EffectId
from
    @combo_ingredient c
    inner join effect_ingredient ec on c.IngredientId = ec.IngredientId
having
    count(*) > 1
group by 
    c.comboId, ec.EffectId

--remove combinations that include an ingredient that do not contribute to an effect
delete from @combo_effect ce
where ce.ComboId in (
    select 
        ci.ComboId 
    from 
        @combo_ingredient ci
        inner join effect_ingredient ei on ci.IngredientId = ei.IngredientId
        left outer join @combo_effect ce on ce.ComboId = ci.ComboId and ce.EffectId = ei.EffectId
    where 
        ce.ComboId is null
)

--you can then query combo_effect for whatever information you want
--all combos with no poison effects
select comboId from 
    @combo_effect ce 
    left outer join effect e on ce.effectId = e.effectId and e.PoisonFlag = 1
group by 
    comboId
having


                        
                        
                            answered 2011-12-14T21:02:51.837

How do I extend this query to find valid combinations of three items?

1 Answer

Your Answer