[thelist] Nested SQL question

[Rudy_Limeback at maritimelife.ca]

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

hi adam

excellent strategy

that happens anyway, under the database covers -- views and even 
subqueries are first translated into joins -- so if you can write them as 
joins, it may aid comprehension, and sometimes you can influence the 
actual execution strategy chosen by the optimizer...


> I don't know much about Access so the syntax is probably a bit off the 
mark 

you have both a left and a right join in the same query, i'm pretty sure 
msaccess will barf on that

plus, you have to be really careful when counting rows that aren't there

when you use an outer join, rows that aren't there get nulls in those 
columns, so counting them at the same time as listing the rows that *are* 
there...  well, that's tricky

especially if you're gonna do both a left and a right join at the same 
time

the count() function typically ignores nulls, too


i don't have a solution, matthew, but i'm getting more and more 
intrigued... 

i have two prior evolt items in my to-do queue, but i think i'd like to 
try testing these queries myself

good thing there's no rush, eh?


rudy
r937.com