StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Not sure how this will perform, but it's a lot simpler I think:</p> <pre><code>select (select Name from Ingredient where ID = e1.UniqIngredient) as Ingredient1, (select Name from Ingredient where ID = jt1.UniqIngredient) as Ingredient2, (select Name from Ingredient where ID = jt2.UniqIngredient) as Ingredient3, ee1.Name as Effect1, ee2.Name as Effect2 from IngredientEffectJT e1 inner join IngredientEffectJT e2 on e1.UniqEffect < e2.UniqEffect and e1.UniqIngredient = e2.UniqIngredient inner join IngredientEffectJT jt1 on jt1.UniqEffect = e1.UniqEffect and e1.UniqIngredient != jt1.UniqIngredient inner join IngredientEffectJT jt2 on jt2.UniqEffect = e2.UniqEffect and e1.UniqIngredient != jt2.UniqIngredient and jt1.UniqIngredient != jt2.UniqIngredient inner join Effect ee1 on e1.UniqEffect = ee1.ID inner join Effect ee2 on e2.UniqEffect = ee2.ID where ee1.Poison = ee2.Poison ; </code></pre> <p>EDIT: Forgot Poison check.</p> <p>EDIT: try2: (edited again to handle any ingredient as shared, not just lowest ID)</p> <pre><code>select (select Name from Ingredient where ID = i1) as Ingredient1, (select Name from Ingredient where ID = i2) as Ingredient2, (select Name from Ingredient where ID = i3) as Ingredient3, min(Poison) as Poison -- , group_concat(Name) as Effects from ( select a.*, min(e.Name) as Name, min(e.Poison) as Poison from ( select -- straight_join i1.ID as i1, i2.ID as i2, i3.ID as i3 from IngredientEffectJT e1 inner join IngredientEffectJT e2 on e1.UniqEffect < e2.UniqEffect and e1.UniqIngredient = e2.UniqIngredient inner join Effect ee1 on e1.UniqEffect = ee1.ID inner join Effect ee2 on e2.UniqEffect = ee2.ID and ee1.Poison = ee2.Poison inner join IngredientEffectJT jt1 on jt1.UniqEffect = e1.UniqEffect and e1.UniqIngredient != jt1.UniqIngredient inner join IngredientEffectJT jt2 on jt2.UniqEffect = e2.UniqEffect and jt1.UniqIngredient != jt2.UniqIngredient and e1.UniqIngredient != jt2.UniqIngredient inner join Ingredient i1 on (i1.ID = e1.UniqIngredient and e1.UniqIngredient < jt1.UniqIngredient and e1.UniqIngredient < jt2.UniqIngredient) or (i1.ID = jt1.UniqIngredient and jt1.UniqIngredient < e1.UniqIngredient and jt1.UniqIngredient < jt2.UniqIngredient) or (i1.ID = jt2.UniqIngredient and jt2.UniqIngredient < jt1.UniqIngredient and jt2.UniqIngredient < e1.UniqIngredient) inner join Ingredient i3 on (i3.ID = e1.UniqIngredient and e1.UniqIngredient > jt1.UniqIngredient and e1.UniqIngredient > jt2.UniqIngredient) or (i3.ID = jt1.UniqIngredient and jt1.UniqIngredient > e1.UniqIngredient and jt1.UniqIngredient > jt2.UniqIngredient) or (i3.ID = jt2.UniqIngredient and jt2.UniqIngredient > jt1.UniqIngredient and jt2.UniqIngredient > e1.UniqIngredient) inner join Ingredient i2 on i2.ID = e1.UniqIngredient + jt1.UniqIngredient + jt2.UniqIngredient - i1.ID - i3.ID group by i1.ID, i2.ID, i3.ID ) as a inner join IngredientEffectJT as jt on a.i1 = jt.UniqIngredient or a.i2 = jt.UniqIngredient or a.i3 = jt.UniqIngredient inner join Effect e on jt.UniqEffect = e.ID group by i1, i2, i3, e.ID having count(*) >= 2 ) as b group by b.i1, b.i2, b.i3 having sum(Poison) = count(*) or sum(Poison) = 0 -- order by count(distinct Name) desc order by i1, i2, i3 ; </code></pre> <p>EDIT3:</p> <p>For SQL Server replace group_concat() line with:</p> <pre><code> ,( ( select min(e.Name) + ',' as [data()] from IngredientEffectJT jt inner join Effect e on jt.UniqEffect = e.ID where i1=jt.UniqIngredient or i2=jt.UniqIngredient or i3=jt.UniqIngredient group by jt.UniqEffect having COUNT(*) >= 2 for xml path('') ) ) as Effects </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