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 ID of the 2 ingredients will always be in the 1st column.
I join both ingredients to the same effect, because I only care about combinations that produce a result.
Then I group them by the 2 ingredients and count up how many poison effects they share because I only want combinations that have 0 poison effects.
Then I use this result as a table that I join back to the Ingredient and Effect tables to get a list of every possible 2 ingredient combination that produces potions, and what effects each combination has:
SELECT i1.Name, i2.Name, e.Name
FROM (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) il
INNER JOIN Ingredient i1 ON il.UniqIngredient1 = i1.UniqIngredient
INNER JOIN Ingredient i2 ON il.UniqIngredient2 = i2.UniqIngredient
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
ORDER BY i1.Name, i2.Name, e.Name
Using the same query I can find 2 ingredient poison combinations that have no potion effects just by changing the HAVING line to check e.Potion instead of e.Poison.
This is all fine and good, but when I want to introduce the 3rd ingredient that's where it gets tricky. I'm stumped. I can modify this query to check for 3 ingredients that all have the same effect, but that's not what I want. I want to find a 3rd ingredient that has a different effect in common with 1 of the ingredients.
Any help?
EDIT
Update: So after struggling with this for hours I have come up with a big, ugly, slow, hard to follow query (I actually don't even remember why I had to do that crazy join condition on the Effect table. But when I change it the whole query is 2x slower so it's actually faster the way I have it, though I don't know why...), that almost does what I want. This might just be as close as I can get, unless someone has any other ideas or sees a way to improve my new query.
SELECT DISTINCT il.Name1, il.Name2, il.Name3, e.Name
FROM
(SELECT DISTINCT i1.UniqIngredient Ingredient1, i1.Name Name1, i2.UniqIngredient Ingredient2, i2.Name Name2, i3.UniqIngredient Ingredient3, i3.Name Name3
FROM Ingredient i1
INNER JOIN Ingredient i2 ON i1.UniqIngredient < i2.UniqIngredient
INNER JOIN Ingredient i3 ON i2.UniqIngredient < i3.UniqIngredient
INNER JOIN IngredientEffectJT jt1 ON i1.UniqIngredient = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON i2.UniqIngredient = jt2.UniqIngredient
INNER JOIN IngredientEffectJT jt3 ON i3.UniqIngredient = jt3.UniqIngredient
INNER JOIN Effect e ON (jt1.UniqEffect = e.UniqEffect AND (jt2.UniqEffect = e.UniqEffect OR jt3.UniqEffect = e.UniqEffect)) OR (jt2.UniqEffect = e.UniqEffect AND jt3.UniqEffect = e.UniqEffect)
WHERE (EXISTS (SELECT 1
FROM IngredientEffectJT jt1
INNER JOIN IngredientEffectJT jt2 ON jt1.UniqEffect = jt2.UniqEffect
WHERE jt1.UniqIngredient = i1.UniqIngredient
AND jt2.UniqIngredient = i2.UniqIngredient)
AND (EXISTS (SELECT 1
FROM IngredientEffectJT jt1
INNER JOIN IngredientEffectJT jt3 ON jt1.UniqEffect = jt3.UniqEffect
WHERE jt1.UniqIngredient = i1.UniqIngredient
AND jt3.UniqIngredient = i3.UniqIngredient)
OR EXISTS (SELECT 1
FROM IngredientEffectJT jt2
INNER JOIN IngredientEffectJT jt3 ON jt2.UniqEffect = jt3.UniqEffect
WHERE jt2.UniqIngredient = i2.UniqIngredient
AND jt3.UniqIngredient = i3.UniqIngredient)))
OR (EXISTS (SELECT 1
FROM IngredientEffectJT jt1
INNER JOIN IngredientEffectJT jt3 ON jt1.UniqEffect = jt3.UniqEffect
WHERE jt1.UniqIngredient = i1.UniqIngredient
AND jt3.UniqIngredient = i3.UniqIngredient)
AND EXISTS (SELECT 1
FROM IngredientEffectJT jt2
INNER JOIN IngredientEffectJT jt3 ON jt2.UniqEffect = jt3.UniqEffect
WHERE jt2.UniqIngredient = i2.UniqIngredient
AND jt3.UniqIngredient = i3.UniqIngredient))
GROUP BY i1.UniqIngredient, i1.Name, i2.UniqIngredient, i2.Name, i3.UniqIngredient, i3.Name
HAVING SUM(e.Poison) = 0) il
INNER JOIN IngredientEffectJT jt1 ON il.Ingredient1 = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON il.Ingredient2 = jt2.UniqIngredient
INNER JOIN IngredientEffectJT jt3 ON il.Ingredient3 = jt3.UniqIngredient
INNER JOIN Effect e ON (jt1.UniqEffect = e.UniqEffect AND (jt2.UniqEffect = e.UniqEffect OR jt3.UniqEffect = e.UniqEffect)) OR (jt2.UniqEffect = e.UniqEffect AND jt3.UniqEffect = e.UniqEffect)
ORDER BY il.Name1, il.Name2, il.Name3, e.Name
In the inner query:
FROM Ingredient i1
INNER JOIN Ingredient i2 ON i1.UniqIngredient < i2.UniqIngredient
INNER JOIN Ingredient i3 ON i2.UniqIngredient < i3.UniqIngredient
This creates every possible combination of 3 ingredients where order does not matter and nothing is repeated. Then the Joins to IngredientEffectJT and Effect... I actually don't remember what the crazy join on Effect is for. Looking at it, I thought it was to ensure an effect exists on at least 2 ingredients, but that's what the WHERE clause is doing. And simplifying that Effect join causes it to run significantly slower so...whatever.
Then the GROUP BY is there so I can count the number of matching poison effects. Since I had to group by the 3 ingredients, I lose the individual matching effects so then I need to rejoin all of those ingredients back to their effects and find the effects that match.
The problem with this query is that it will show combinations where all 3 ingredients have the same 1 effect. Those combinations are pointless because you can make the same thing by only using 2 of those 3 so it's kind of wasteful.
So, this is the best I could come up with. It's really slow so maybe I'll just save it to a new table to make it easier and faster to query again in the future.
While Martin Smith's solution does not entirely solve this problem, it did inspire me to look into using CTEs and I think I got it.
Once I realized every 3 ingredient combination is really 2 different 2 ingredient combinations that share 1 common ingredient, I decided to find all 2 ingredient combinations and then find every combination of those that have at least 1 ingredient in common and both have effects that the other does not have.
Then check to make sure every 3 ingredient combination has no poison effects (I already know each individual 2 ingredient combination has no poison effects, but just because A+B has no poison and B+C has no poison doesn't mean A+B+C won't have poison. It's possible that combing A with C will produce a poison effect).
Then I join all 3 ingredients back to the Effect table to display which effects are produced with each combination.
This query has a 3 minute 50 second execution time on my system. That's not cool. But at least I am getting the results I want now.
WITH Combination AS
(
--Finds all 2 ingredient combinations that have shared effects that are not poisons
select ROW_NUMBER() OVER (ORDER BY i1.Name, i2.Name) UniqCombination, i1.UniqIngredient UniqIngredient1, i2.UniqIngredient UniqIngredient2, COUNT(1) NumberOfEffects
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, i1.name, i2.UniqIngredient, i2.Name
HAVING SUM(e.poison) = 0
),
Potion AS
(
--Matches up all 2 ingredient combinations in the Combination CTE with the effects for that combination
SELECT DISTINCT c.UniqCombination, c.UniqIngredient1, i1.Name Ingredient1, c.UniqIngredient2, i2.Name Ingredient2, e.UniqEffect, e.Name Effect
FROM Combination c
INNER JOIN Ingredient i1 ON c.UniqIngredient1 = i1.UniqIngredient
INNER JOIN Ingredient i2 ON c.UniqIngredient2 = i2.UniqIngredient
INNER JOIN IngredientEffectJT jt1 ON c.UniqIngredient1 = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON c.UniqIngredient2 = jt2.UniqIngredient
INNER JOIN Effect e ON jt1.UniqEffect = e.UniqEffect AND jt2.UniqEffect = e.UniqEffect
),
BigCombination AS
(
--Matches 2 combinations together where 1 ingredient is the same in both combinations.
SELECT c1.UniqIngredient1, CASE WHEN c1.UniqIngredient1 = c2.UniqIngredient1 THEN c1.UniqIngredient2 ELSE c2.UniqIngredient1 END UniqIngredient2, c2.UniqIngredient2 UniqIngredient3
FROM Combination c1
INNER JOIN Combination c2 ON (c1.UniqIngredient1 = c2.UniqIngredient1 OR c1.UniqIngredient2 = c2.UniqIngredient1 OR c1.UniqIngredient2 = c2.UniqIngredient2) AND c1.UniqCombination < c2.UniqCombination
--This WHERE clause sucks because there are 2 different select queries that must run twice each.
--They have to run twice because I have to EXCEPT 1 from 2 and 2 from 1 to make sure both combinations are contributing something new.
WHERE EXISTS( SELECT p1.UniqEffect
FROM Potion p1
WHERE p1.UniqCombination = c1.UniqCombination
EXCEPT
SELECT p2.UniqEffect
FROM Potion p2
WHERE p2.UniqCombination = c2.UniqCombination)
AND EXISTS( SELECT p2.UniqEffect
FROM Potion p2
WHERE p2.UniqCombination = c2.UniqCombination
EXCEPT
SELECT p1.UniqEffect
FROM Potion p1
WHERE p1.UniqCombination = c1.UniqCombination)
),
BigPotionCombination AS
(
--Combinations were made only from other combinations that made potions, but it's possible the new
--ingredients mixing together could create a new poison effect. This will remove combinations that create new poison effects
SELECT DISTINCT c.*
FROM BigCombination c
INNER JOIN IngredientEffectJT jt1 ON c.UniqIngredient1 = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON c.UniqIngredient2 = jt2.UniqIngredient
INNER JOIN IngredientEffectJT jt3 ON c.UniqIngredient3 = jt3.UniqIngredient
INNER JOIN Effect e ON (jt1.UniqEffect = e.UniqEffect AND (jt2.UniqEffect = e.UniqEffect OR jt3.UniqEffect = e.UniqEffect)) OR (jt2.UniqEffect = e.UniqEffect AND jt3.UniqEffect = e.UniqEffect)
GROUP BY c.UniqIngredient1, c.UniqIngredient2, c.UniqIngredient3
HAVING SUM(e.Poison) = 0
)
--Combinations have to be joined back to Effect again to display the effects that the potions have.
SELECT DISTINCT i1.Name Ingredient1, i2.Name Ingredient2, i3.Name Ingredient3, e.Name Effect
FROM BigPotionCombination c
INNER JOIN Ingredient i1 ON c.UniqIngredient1 = i1.UniqIngredient
INNER JOIN Ingredient i2 ON c.UniqIngredient2 = i2.UniqIngredient
INNER JOIN Ingredient i3 ON c.UniqIngredient3 = i3.UniqIngredient
INNER JOIN IngredientEffectJT jt1 ON c.UniqIngredient1 = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON c.UniqIngredient2 = jt2.UniqIngredient
INNER JOIN IngredientEffectJT jt3 ON c.UniqIngredient3 = jt3.UniqIngredient
INNER JOIN Effect e ON (jt1.UniqEffect = e.UniqEffect AND (jt2.UniqEffect = e.UniqEffect OR jt3.UniqEffect = e.UniqEffect)) OR (jt2.UniqEffect = e.UniqEffect AND jt3.UniqEffect = e.UniqEffect)
ORDER BY Ingredient1, Ingredient2, Ingredient3, Effect
