[thelist] Nested SQL question
apatrick at oracular.com
apatrick at oracular.com
Tue Sep 12 09:42:47 CDT 2000
Hi Matt,
<||easiest solution to get to from the current state||>
Assuming UserID is a column in the User_Ingredients table: You should be able
to specify UserID as a criterion (where userid = x) in your second query (the join
between missingingredients and recipes) and leave your first query (the view)
alone :-). Something like this:
SELECT Count([Miss...
FROM MissingIngredients RIGHT JOIN Recipes
ON [MissingIngredients].[RecipeID]=[Recipes].[RecipeID]
--||> WHERE UserID = X <||--
GROUP BY ...
ORDER BY ...;
<||/easiest solution to get to from the current state||>
<||more efficient solution||>
Another way of accomplishing this task would be to join all 3 (recipes,
ingredients, and useringredients) tables using the same criteria you already have
present (from both queries). Something *like* this:
SELECT Recipes_Ingredients.RecipeID,
Count([MissingIngredients].[RecipeID]) AS CountMissingIngredients,
[Recipes].[RecipeID], [Recipes].[RecipeName]
FROM Recipes_Ingredients LEFT JOIN Users_Ingredients ON
Recipes_Ingredients.IngredientID = Users_Ingredients.IngredientID
RIGHT JOIN Recipes ON
[MissingIngredients].[RecipeID]=[Recipes].[RecipeID]
WHERE (((Users_Ingredients.IngredientID) Is Null))
and UserID = X
GROUP BY [Recipes].[RecipeID], [Recipes].[RecipeName]
ORDER BY Count([MissingIngredients].[RecipeID]), [Recipes].[RecipeName]
I don't know much about Access so the syntax is probably a bit off the mark but
the concept should work.
<||/more efficient solution||>
===========================================================
"Matthew Walker" <||matthew at electricsheep.co.nz||> wrote:
I have two queries for an application that finds recipes you can make with
the ingredients you have onhand.
MissingIngredients:
SELECT Recipes_Ingredients.RecipeID
FROM Recipes_Ingredients LEFT JOIN Users_Ingredients ON
Recipes_Ingredients.IngredientID = Users_Ingredients.IngredientID
WHERE (((Users_Ingredients.IngredientID) Is Null));
Recipes:
SELECT Count([MissingIngredients].[RecipeID]) AS CountMissingIngredients,
[Recipes].[RecipeID], [Recipes].[RecipeName]
FROM MissingIngredients RIGHT JOIN Recipes ON
[MissingIngredients].[RecipeID]=[Recipes].[RecipeID]
GROUP BY [Recipes].[RecipeID], [Recipes].[RecipeName]
ORDER BY Count([MissingIngredients].[RecipeID]), [Recipes].[RecipeName];
OK so that works. I just have two queries in Access with one calling the
other. But I want to be able to specify a UserID which will mean the first
query has to be dynamic, constructed by the server. So I guess I need to
combine these two queries into one nested query. How do I do that? I just
don't get it.
============================================================
More information about the thelist
mailing list