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 > > ______________________________________________________________________ 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/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
