[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