[SQL]How to select based on many-to-many relationship. - Tilted Forum Project Discussion Community

jakal · 08-19-2004, 04:36 PM

I'm embarrassed to ask this, but I have limited SQL experience.
I have a many to many relationship, and I need to select rows in one table based on more than one value in the other table. For example if you have a recipe table, an ingredient table, and an intersection relation then what would you need to do select all recipes based on two or more ingredients?
So the tables would be:
RECIPE(RecipeID, RecipeName)
INGREDIENT(IngredientID,IngredientName)
RECIPEINGREDIENT(IngredientID, RecipeID)

MrFlux · 08-19-2004, 05:08 PM

Wow... I just can't figure this one out O_o

Something using a subquery maybe?

welshbyte · 08-20-2004, 08:11 AM

Please note this is totally untested but it seems logical to me and should be syntactically correct. Its worth a try anyway. SQL gurus reading this may have a more efficient way to do it too, i'm far from an expert. Anyway, here goes:

Code:

SELECT *
FROM RECIPE
WHERE RECIPEINGREDIENT.RecipeID = RECIPE.RecipeID
AND RECIPEINGREDIENT.IngredientID = INGREDIENT.IngredientID
AND (INGREDIENT.IngredientName LIKE 'Eggs' OR INGREDIENT.IngredientName LIKE 'Bacon');

I've used 'Eggs' and 'Bacon' as examples here but more ingredients would be added by extending with more OR's between the parentheses.

Worth a try

welshbyte

jakal · 08-20-2004, 03:24 PM

That looks good. I'll try it out. I got it working using a subquery, but this may be better. It is certainly more simple and easier to understand!

magua · 08-20-2004, 08:14 PM

Use EXISTS checks. Eg:

Code:

SELECT RecipeName
FROM Recipe
WHERE EXISTS (
     SELECT *
     FROM RecipeIngredient
     WHERE RecipeIngredient.RecipeID = Recipe.RecipeID
     AND RecipeIngredit.IngredientID = <Ingredient1ID>
) AND EXISTS (
     SELECT *
     FROM RecipeIngredient
     WHERE RecipeIngredient.RecipeID = Recipe.RecipeID
     AND RecipeIngredit.IngredientID = <Ingredient2ID>
) ...etc

Welshbyte's answer will return recipes that match only one ingredient.

mino · 08-23-2004, 03:12 AM

Another way of doing this is to do:

Code:

SELECT RecipeName
FROM Recipe
INNER JOIN RecipeIngredient AS Ingredient1 ON
     (Ingredient1.RecipeID = Recipe.RecipeID)
INNER JOIN RecipeIngredient AS Ingredient2 ON
     (Ingredient2.RecipeID = Recipe.RecipeID)
WHERE
     (Ingredient1.IngredientID = <IngredientID1>)
     AND (Ingredient2.IngredientID = <IngredientID2>)

08-19-2004, 04:36 PM	#1 (permalink)
jakal Upright	[SQL]How to select based on many-to-many relationship. I'm embarrassed to ask this, but I have limited SQL experience. I have a many to many relationship, and I need to select rows in one table based on more than one value in the other table. For example if you have a recipe table, an ingredient table, and an intersection relation then what would you need to do select all recipes based on two or more ingredients? So the tables would be: RECIPE(RecipeID, RecipeName) INGREDIENT(IngredientID,IngredientName) RECIPEINGREDIENT(IngredientID, RecipeID)

08-19-2004, 05:08 PM	#2 (permalink)
MrFlux Fluxing wildly... Location: Auckland, New Zealand	Wow... I just can't figure this one out O_o Something using a subquery maybe? __________________ flux (n.) Medicine. The discharge of large quantities of fluid material from the body, especially the discharge of watery feces from the intestines.

08-20-2004, 08:11 AM	#3 (permalink)
welshbyte Insane Location: Wales, UK, Europe, Earth, Milky Way, Universe	Please note this is totally untested but it seems logical to me and should be syntactically correct. Its worth a try anyway. SQL gurus reading this may have a more efficient way to do it too, i'm far from an expert. Anyway, here goes: Code: SELECT * FROM RECIPE WHERE RECIPEINGREDIENT.RecipeID = RECIPE.RecipeID AND RECIPEINGREDIENT.IngredientID = INGREDIENT.IngredientID AND (INGREDIENT.IngredientName LIKE 'Eggs' OR INGREDIENT.IngredientName LIKE 'Bacon'); I've used 'Eggs' and 'Bacon' as examples here but more ingredients would be added by extending with more OR's between the parentheses. Worth a try welshbyte __________________ There are only two industries that refer to their customers as "users". - Edward Tufte

08-20-2004, 03:24 PM	#4 (permalink)
jakal Upright	That looks good. I'll try it out. I got it working using a subquery, but this may be better. It is certainly more simple and easier to understand!