StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try this</p> <pre><code>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 Count(e.id) = 0 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload