Yes, but that's the ansi syntax, but MS Access isn't an ANSI compliant database. Although it's close, AFAIK there are limitations on outer joins in access that aren't there in sql server. If you want more than one, you have to build extra queries and join those in.
-----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 10:52 AM To: CF-Talk Subject: Re: Left Outer Join - Lotsa Pain! mark the ansi syntax for the left outer join goes like something like this: FROM tablename w LEFT OUTER JOIN tablename e ON w.foreignkey = e.primarykey LEFT OUTER JOIN tablename s ON w.foreignkey = s.primarykey LEFT OUTER JOIN tablename a ON w.primarykey = a.foreignkey and margaret made a good point...best place to learn is in access query builder (design view). once you get in there, switch to sql view to see all. :) ~ dina ----- Original Message ----- From: "Mark Leder" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, May 06, 2002 11:46 AM Subject: RE: Left Outer Join - Lotsa Pain! > So how would I write it for Access? Would I still use the WHERE > statement? It's the syntax for Access that really throws me for a > loop! > > FROM pl_companies LEFT OUTER JOIN (pl_category, pl_join, > pl_joincatbrand, pl_brands) > > Thanks, > Mark > > > -----Original Message----- > From: Dina Hess [mailto:[EMAIL PROTECTED]] > Sent: Monday, May 06, 2002 12:13 PM > To: CF-Talk > Subject: Re: Left Outer Join - Lotsa Pain! > > > mark, > > try using left outer join to join *all* of your tables, starting with > pl_companies and ending with pl_brands. > > ~ dina > > > > > WHERE pl_category.prl_cat_ID = #FORM.prl_cat_ID# AND > > pl_category.prl_cat_ID = pl_joincatbrand.prl_cat_ID AND > > pl_Brands.pl_BID = pl_joincatbrand.pl_BID AND pl_Brands.pl_BID = > > pl_join.pl_BID AND pl_join.pl_ID = pl_companies.pl_ID > > > > ORDER BY pl_companies.co_name ASC > > > ----- Original Message ----- > From: "Mark Leder" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Monday, May 06, 2002 10:10 AM > Subject: Left Outer Join - Lotsa Pain! > > > > This query is being run on MSAccess 2002. > > > > I want to do a LEFT OUTER JOIN so that all company names are > returned > > based on a product CATEGORY, even though they may not have a > product > > BRAND associated with them. (I used a joining table, pl_join, > because > > one company may have many brands; the pl_joincatbrand table is > used as a > > joining table because one brand may belong to many categories). > > > > The query statement below works properly, but will not bring > back > > company names that do not have a brand. I've tried a number of > ways to > > do a LOJ in the FROM statement, but I can't get it to work > where there > > are more than two tables involved. (ie, FROM pl_companies LEFT > OUTER > > JOIN pl_join ON pl_companies.pl_ID = pl_join.pl_ID) > > > > Your help is greatly appreciated, as my head is getting really > sore > > banging it on the desk for the past 7 hours trying to get this > to work > > right. > > > > Mark > > ================================ > > > > SELECT * > > FROM pl_category, pl_join, pl_companies, pl_joincatbrand, > pl_brands > > > > WHERE pl_category.prl_cat_ID = #FORM.prl_cat_ID# AND > > pl_category.prl_cat_ID = pl_joincatbrand.prl_cat_ID AND > > pl_Brands.pl_BID = pl_joincatbrand.pl_BID AND pl_Brands.pl_BID = > > pl_join.pl_BID AND pl_join.pl_ID = pl_companies.pl_ID > > > > ORDER BY pl_companies.co_name ASC > > > > Thanks, > > Mark > > > > > > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
