Uh... Sort of... I can't remember, but MS Query seems to put out sql in an ansi-92 style... Joins in the where clause instead of the from clause... It may work with ODBC well, but it looks terrible...
Honestly, MS Access has the best query designer out there... You could always write them by hand like most people do... I may sound stupid here, but what on earth is a row attribute in terms of a query? -----Original Message----- From: LANCASTER, STEVEN M. (JSC-OL) (BAR) [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 10:51 AM To: CF-Talk Subject: RE: Query Tool Set (was: Left Outer Join - Lotsa Pain!) You can use MSQuery. Steven Lancaster Barrios Technology NASA/JSC 281-244-2444 (voice) [EMAIL PROTECTED] -----Original Message----- From: Mark Leder [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 12:48 PM To: CF-Talk Subject: Query Tool Set (was: Left Outer Join - Lotsa Pain!) I'd love to use a Query tool - but I think the MS Access tool is tough to work with. Drawing lines and connectors is easy enough, but editing row attributes I think is counterintuitive. I can't seem to get rid of a row if I make a mistake unless I start over entirely - can't seem to delete a row. Anyone have any suggestions on other Query Builder tools that they like? I'd like to use the one included with CF 5, but every time I run a query, it crashes my machine and eats all the memory. Thanks, Mark -----Original Message----- From: Margaret Fisk [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 12:47 PM To: CF-Talk Subject: RE: Left Outer Join - Lotsa Pain! The easiest way to get the Access SQL is to construct the statement in the Access GUI. Then you can test out your query as well. -----Original Message----- From: Mark Leder [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 9:47 AM To: CF-Talk 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 > > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com 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
