[thelist] Nested SQL question

[apatrick at oracular.com]

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.

============================================================