ok, here's what i found in an access db: FROM Suppliers LEFT JOIN (Mfrs LEFT JOIN (Departments LEFT JOIN Products ON Departments.DepartmentID = Products.DepartmentID) ON Mfrs.MfrID = Products.MfrID) ON Suppliers.SupplierID = Products.SupplierID
no wonder you're having problems, mark. :) ~ dina ----- Original Message ----- From: "Costas Piliotis" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, May 06, 2002 1:09 PM Subject: RE: Left Outer Join - Lotsa Pain! > 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 > > > > > > > > > > > > > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
