[thelist] Nested SQL question

Matthew Walker matthew at electricsheep.co.nz
Sat Sep 9 23:34:47 CDT 2000


I have two queries for an application that finds recipes you can make with
the ingredients you have onhand.

One query builds a list returning a RecipeID for each missing ingredient (so
a recipe with two missing ingredients would appear on the list twice). The
other query takes this result and provides a list of recipes ordered by the
number of missing ingredients (i.e. no missing ingredients first).


SELECT Recipes_Ingredients.RecipeID
FROM Recipes_Ingredients LEFT JOIN Users_Ingredients ON
Recipes_Ingredients.IngredientID = Users_Ingredients.IngredientID
WHERE (((Users_Ingredients.IngredientID) Is Null));


SELECT Count([MissingIngredients].[RecipeID]) AS CountMissingIngredients,
[Recipes].[RecipeID], [Recipes].[RecipeName]
FROM MissingIngredients RIGHT JOIN Recipes ON
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.

Thank you,

More information about the thelist mailing list