[thelist] SQL: looking for resources, have one question
rudy
r937 at interlog.com
Tue Oct 22 16:29:00 CDT 2002
thanks for the table layouts, they helped me understand
here's a query that lists all products and their adverts
select ppName
, paname
, ptComments
, ptUsername
, ptDate
from PossibleProducts
left outer join
( productTracking
left outer join PossibleAdverts
on ptReferralSource = PossibleAdverts.id )
on PossibleProducts.id = ptProduct
order by ppName, ptDate
notice that i have used parentheses -- microsoft access wants those
another quirk of access is that if the first join is outer, the rest must
also be
when you try this, arrange for there to be at least one PossibleProducts
row without any adverts -- that situation is what the left outer joins will
handle
if you don't care about products that don't have adverts, then you can make
them inner joins
finally, after you paste this query into the View > SQL View window of the
access query builder, click on View > Design View, and voila, a nice
diagram
now doubleclick one of the arrows, it should say Join Properties and tell
you "Include ALL rows of one table and only those rows of the other where
joined fields are equal"
rudy
More information about the thelist
mailing list