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

Tilted Forum Project Discussion Community (https://thetfp.com/tfp/)

- Tilted Technology (https://thetfp.com/tfp/tilted-technology/)

- - [SQL]How to select based on many-to-many relationship. (https://thetfp.com/tfp/tilted-technology/66323-sql-how-select-based-many-many-relationship.html)

jakal

08-19-2004 04:36 PM

[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)

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>)

All times are GMT -8. The time now is 02:38 AM.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73