[thelist] Nested SQL question

[rudy limeback]

hi matthew

thanks for bringing this to thelist

you gotta know i'm all over it, right?    ;o)


but then i got this feeling, like, is that fair?  why always me?

so if anybody wants to give this a shot, jump right in

i know it's sunday, on this side of the planet, anyway -- i think you and
oliver are into monday already, right matthew? -- and there probably aren't
a lot of folks online at the moment

so let me make some comments on your problem, matthew, while you let us
know how soon you need this, and i'll backstop ya in case nobody else takes
it


> 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).
>
>SELECT Recipes_Ingredients.RecipeID
>FROM Recipes_Ingredients LEFT JOIN Users_Ingredients ON
>Recipes_Ingredients.IngredientID = Users_Ingredients.IngredientID
>WHERE (((Users_Ingredients.IngredientID) Is Null));

hey, an outer join!  excellent!

this is also a great example of *omitting* the DISTINCT keyword

if this had said SELECT DISTINCT Recipes_Ingredients.RecipeID
you would get a simple list of the (unique) recipes that the user cannot
make because at least one ingredient is missing

knowing which recipes the user cannot make is useful, for example, if you
wanted to list only the recipes that the user *can* make

but you were looking for the *number* of missing ingredients, which of
course means you want all the rows of the outer join

your problem, matthew, was stated almost completely in database terms, and
i know it's not polite to inquire into areas that are outsite the bounds of
the question (i get shit all the time at work for doing this)...

... but i have a question that comes after the next query


> 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 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];

this is where i always get into trouble

(patient: "doc, it hurts when i do that"   doc: "don't do that")

why do you want to list *all* the recipes including the ones the user can't
make?

i would think people who are interested in following a recipe -- why are
they at the site, eh --  would not be interested in recipes they can't make

maybe give them a choice -- list all recipes they can make, or list all
recipes in order by number of missing ingredients, starting with 0, that
they, um, don't have...

i would have to question whether the *number* of missing ingredients
matters -- other than, yes, being a most excellent sort control if you were
going to list recipes including ones the user cannot make

but that's just me    ;o)


> OK so that works. I just have two queries in Access
> with one calling the other.

i wonder how many people caught that

the second query *calls the first*

this is how you do a View in msaccess (that's microsoft, eh?)

in other datases the first query would be a View defined on the Table


> But I want to be able to specify a UserID which will mean the first
> query has to be dynamic, constructed by the server.

yes, you would ordinarily just add another condition to the WHERE clause,
but since you are not actually executing the first query, you cannot do
that, since you are only running the second query from the web server, the
second query calls the first... in another database, using a View, there
would be only the second, i.e. single query...

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

well, they are, sorta, already nested -- if you know what i mean   ;o)


how soon do you have to have this working, matthew?


rudy
r937.com