How do I extend this query to find valid combinations of three items?
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.IDfrom 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 ingredientsdeclare @combo_ingredient table (ComboId int, IngredientId int)--insert into the mapping tableinsert into @combo_ingredient (ComboId, IngredientId)select ID, ingredient1 from @combosinsert into @combo_ingredient (ComboId, IngredientId)select ID, ingredient1 from @combosinsert 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 havedeclare @combo_effect (comboId int, effectId int)insert into @combo_effect (comboId, effectId)select c.ComboId, ec.EffectIdfrom @combo_ingredient c inner join effect_ingredient ec on c.IngredientId = ec.IngredientIdhaving count(*) > 1group by c.comboId, ec.EffectId--remove combinations that include an ingredient that do not contribute to an effectdelete from @combo_effect cewhere 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 effectsselect comboId from @combo_effect ce left outer join effect e on ce.effectId = e.effectId and e.PoisonFlag = 1group by comboIdhaving Count(e.id) = 0
Here's one go at it.
;WITH IngredientCombinations AS(SELECT i1.UniqIngredient AS i1_UniqIngredient, i1.Name AS i1_Name, i2.UniqIngredient AS i2_UniqIngredient, i2.Name AS i2_Name, i3.UniqIngredient AS i3_UniqIngredient, i3.Name AS i3_Name, i1.UniqIngredient AS i1_UniqIngredientB, i2.UniqIngredient AS i2_UniqIngredientB, i3.UniqIngredient AS i3_UniqIngredientB FROM Ingredient i1 JOIN Ingredient i2 ON i1.UniqIngredient < i2.UniqIngredient JOIN Ingredient i3 ON i2.UniqIngredient < i3.UniqIngredient ), UnpivotedIngredientCombinations AS(SELECT i1_UniqIngredient, i1_Name, i2_UniqIngredient, i2_Name, i3_UniqIngredient, i3_Name, UniqIngredientFROM IngredientCombinationsUNPIVOT (UniqIngredient FOR idx IN (i1_UniqIngredientB, i2_UniqIngredientB, i3_UniqIngredientB))AS unpvt),Effects AS(SELECT uic.i1_Name, uic.i1_UniqIngredient, uic.i2_Name, uic.i2_UniqIngredient, uic.i3_Name, uic.i3_UniqIngredient, uic.UniqIngredient, e.Name, e.Poison, e.Potion, e.UniqEffect, COUNT(*) OVER (PARTITION BY i1_UniqIngredient, i2_UniqIngredient, i3_UniqIngredient, e.UniqEffect) AS CntFROM UnpivotedIngredientCombinations uic JOIN IngredientEffectJT iej ON iej.UniqIngredient = uic.UniqIngredient JOIN Effect e ON e.UniqEffect = iej.UniqEffect )SELECT i1_Name, i2_Name, i3_NameFROM EffectsGROUP BY i1_UniqIngredient, i2_UniqIngredient, i3_UniqIngredient, i1_Name, i2_Name, i3_NameHAVING MAX(CASE WHEN Cnt = 2 AND Poison = 1 THEN 1 END) IS NULL
Ok, so here's my shot at it.
It is based on these requirements:
- Potions only, no poison effect allowed
- Two or three ingredients
- No duplicates (1-2-3, 3-2-1 are the same)
- All ingredients must be contributing to an effect
- Ingredients that are trippling an effect must be excluded unless the provide another effect
I hope the table and field names are ok, I started with my own tables but with your data.
select ing1.name, ing2.name, coalesce(ing3.name, ' ') from(-- Gives all unique combinations of two or three ingredients select ing1.UniqIngredient as id1, ing2.UniqIngredient as id2, 0 as id3 from Ingredient as ing1 inner join Ingredient as ing2 on ing1.UniqIngredient < ing2.UniqIngredient UNION select ing1.UniqIngredient as id1, ing2.UniqIngredient as id2, ing3.UniqIngredient as id3 from Ingredient as ing1 inner join Ingredient as ing2 on ing1.UniqIngredient < ing2.UniqIngredient inner join Ingredient as ing3 on ing2.UniqIngredient < ing3.UniqIngredient) as MainRequestjoin Ingredient as ing1 on ing1.UniqIngredient = id1join Ingredient as ing2 on ing2.UniqIngredient = id2left outer join Ingredient as ing3 on ing3.UniqIngredient = id3where( -- Check if ingredients have common positive effects that are not covered by 3 ingredients (when a 3rd ingredient is present) exists( select eff.UniqEffect, count(*) from /Effect eff join IngredientEffectJT link on link.UniqEffect = eff.UniqEffect where eff.potion = 1 and link.UniqIngredient in (id1, id2, id3) group by eff.UniqEffect having count(*) = 2) AND not exists( select eff.UniqEffect, count(*) from Effect eff join IngredientEffectJT link on link.UniqEffect = eff.UniqEffect where eff.potion = 1 and link.UniqIngredient in (id1, id2, id3) group by eff.UniqEffect having count(*) > 2 ))-- Check if ingredients have no common negative effectsAND not exists( select eff.UniqEffect, count(*) from Effect eff join IngredientEffectJT link on link.UniqEffect = eff.UniqEffect where eff.poison = 1 and link.UniqIngredient in (id1, id2, id3) group by eff.UniqEffect having count(*) >= 2)-- Check if every ingredient is participating (No alchemist likes a freerider)AND exists(select link1.UniqIngredient from IngredientEffectJT link1 inner join IngredientEffectJT link2 on link1.UniqEffect = link2.UniqEffect where link1.UniqIngredient = id1 and link2.UniqIngredient in (id2, id3))AND exists(select link1.UniqIngredient from IngredientEffectJT link1 inner join IngredientEffectJT link2 on link1.UniqEffect = link2.UniqEffect where link1.UniqIngredient = id2 and link2.UniqIngredient in (id1, id3))AND (id3 = 0 or exists(select link1.UniqIngredient from IngredientEffectJT link1 inner join IngredientEffectJT link2 on link1.UniqEffect = link2.UniqEffect where link1.UniqIngredient = id3 and link2.UniqIngredient in (id1, id2)))