Have a Nice Day for Everybody

I hope I  will have similarly nice day if somebody can give  me solution . I
have  several tables and among them there are 3 tables (tblRecipe,
tblIngredient and  tblRecipeIngredient)   which ought to be used  for SQL.
The appropriate relationship is as follows:
tblrecipe (1) -> (n) tblRecipeIngredient  (n) <- (1) tblIngredient,  since a
recipe is composed of many ingredients and the same ingredient can be used
in many  recipes. Therefore tblRecipeIngredient can be considered as
intermediate table for crating  many-to-many relationship.


I would like to make a query (or SQL...) in order to choose out all recipes
in database which contain certain ingredient (IngredientID)
When I must choose only one ingredient the SQL,  like next one , is working
well

Select * FROM tblRecipe  INNER JOIN  tblRecipeIngredient  ON
tblRecipe.RecipeID=tblRecipeIngredient.RecipeID
WHERE IngredientID=x

But I would like to find all recipes which, for example, contain
simultaneously 2 or 3 pre-determined ingredient. Using AND  operator in
WHERE clause I don't get required results.

What can I do it?


tblRecipe:
RecipeID
Recipename
Ingredient
....

tblIngredients
IngredientID
Name
Description
..
tblRecipesIngredients
IngredientRecipeID
RecipeID
IngredientID
Quantity

Best regards

ciao


Peter

Hi Peter

Could you send the non-working SQL?

Brian

>  from:    Bohus Peter <peter.bohus@l...>
>  date:    Tue, 18 Jun 2002 11:05:13
>  to:      access@p...
>  subject: Re: [access] recipes treatment - SQL PROBLEM
> 
> Have a Nice Day for Everybody
> 
> I hope I  will have similarly nice day if somebody can give  me solution . I
> have  several tables and among them there are 3 tables (tblRecipe,
> tblIngredient and  tblRecipeIngredient)   which ought to be used  for SQL.
> The appropriate relationship is as follows:
> tblrecipe (1) -> (n) tblRecipeIngredient  (n) <- (1) tblIngredient,  since a
> recipe is composed of many ingredients and the same ingredient can be used
> in many  recipes. Therefore tblRecipeIngredient can be considered as
> intermediate table for crating  many-to-many relationship.
> 
> 
> I would like to make a query (or SQL...) in order to choose out all recipes
> in database which contain certain ingredient (IngredientID)
> When I must choose only one ingredient the SQL,  like next one , is working
> well
> 
> Select * FROM tblRecipe  INNER JOIN  tblRecipeIngredient  ON
> tblRecipe.RecipeID=tblRecipeIngredient.RecipeID
> WHERE IngredientID=x
> 
> But I would like to find all recipes which, for example, contain
> simultaneously 2 or 3 pre-determined ingredient. Using AND  operator in
> WHERE clause I don't get required results.
> 
> What can I do it?
> 
> 
> tblRecipe:
> RecipeID
> Recipename
> Ingredient
> ....
> 
> tblIngredients
> IngredientID
> Name
> Description
> ..
> tblRecipesIngredients
> IngredientRecipeID
> RecipeID
> IngredientID
> Quantity
> 
> Best regards
> 
> ciao
> 
> 
> Peter
> 

Change your SQL to something like this:

WHERE IngredientID In(a,b,c,d,e)

That assumes IngredientID is a numeric value if it is a string then
WHERE IngredientID In('a','b','c','d','e')

|-----Original Message-----
|From: Bohus Peter [mailto:peter.bohus@l...]
|Sent: Tuesday, June 18, 2002 3:05 AM
|To: Access
|Subject: [access] recipes treatment - SQL PROBLEM
|Importance: High
|
|
|Have a Nice Day for Everybody
|
|I hope I  will have similarly nice day if somebody can give  me
|solution . I
|have  several tables and among them there are 3 tables (tblRecipe,
|tblIngredient and  tblRecipeIngredient)   which ought to be used  for SQL.
|The appropriate relationship is as follows:
|tblrecipe (1) -> (n) tblRecipeIngredient  (n) <- (1)
|tblIngredient,  since a
|recipe is composed of many ingredients and the same ingredient can be used
|in many  recipes. Therefore tblRecipeIngredient can be considered as
|intermediate table for crating  many-to-many relationship.
|
|
|I would like to make a query (or SQL...) in order to choose out all recipes
|in database which contain certain ingredient (IngredientID)
|When I must choose only one ingredient the SQL,  like next one , is working
|well
|
|Select * FROM tblRecipe  INNER JOIN  tblRecipeIngredient  ON
|tblRecipe.RecipeID=tblRecipeIngredient.RecipeID
|WHERE IngredientID=x
|
|But I would like to find all recipes which, for example, contain
|simultaneously 2 or 3 pre-determined ingredient. Using AND  operator in
|WHERE clause I don't get required results.
|
|What can I do it?
|
|
|tblRecipe:
|RecipeID
|Recipename
|Ingredient
|....
|
|tblIngredients
|IngredientID
|Name
|Description
|..
|tblRecipesIngredients
|IngredientRecipeID
|RecipeID
|IngredientID
|Quantity
|
|Best regards
|
|ciao
|
|
|Peter
|

Leo,
that was my first thought too, but just using IN by itself gives you recipes
that have any one of the ingredients, but I think he needs a way to find
recipes that have all of the listed ingredients.

-----Original Message-----
From: Leo Scott [mailto:leoscott@c...]
Sent: Tuesday, June 18, 2002 11:07 AM
To: Access
Subject: [access] RE: recipes treatment - SQL PROBLEM


Change your SQL to something like this:

WHERE IngredientID In(a,b,c,d,e)

That assumes IngredientID is a numeric value if it is a string then
WHERE IngredientID In('a','b','c','d','e')

|-----Original Message-----
|From: Bohus Peter [mailto:peter.bohus@l...]
|Sent: Tuesday, June 18, 2002 3:05 AM
|To: Access
|Subject: [access] recipes treatment - SQL PROBLEM
|Importance: High
|
|
|Have a Nice Day for Everybody
|
|I hope I  will have similarly nice day if somebody can give  me
|solution . I
|have  several tables and among them there are 3 tables (tblRecipe,
|tblIngredient and  tblRecipeIngredient)   which ought to be used  for SQL.
|The appropriate relationship is as follows:
|tblrecipe (1) -> (n) tblRecipeIngredient  (n) <- (1)
|tblIngredient,  since a
|recipe is composed of many ingredients and the same ingredient can be used
|in many  recipes. Therefore tblRecipeIngredient can be considered as
|intermediate table for crating  many-to-many relationship.
|
|
|I would like to make a query (or SQL...) in order to choose out all recipes
|in database which contain certain ingredient (IngredientID)
|When I must choose only one ingredient the SQL,  like next one , is working
|well
|
|Select * FROM tblRecipe  INNER JOIN  tblRecipeIngredient  ON
|tblRecipe.RecipeID=tblRecipeIngredient.RecipeID
|WHERE IngredientID=x
|
|But I would like to find all recipes which, for example, contain
|simultaneously 2 or 3 pre-determined ingredient. Using AND  operator in
|WHERE clause I don't get required results.
|
|What can I do it?
|
|
|tblRecipe:
|RecipeID
|Recipename
|Ingredient
|....
|
|tblIngredients
|IngredientID
|Name
|Description
|..
|tblRecipesIngredients
|IngredientRecipeID
|RecipeID
|IngredientID
|Quantity
|
|Best regards
|
|ciao
|
|
|Peter
|