Ray, use the Access query builder wizard. Select your two tables. If the join line between them doesn't show up automatically, drag the field you want to join from one table to the other. Right click on the line and read the join options. One of the three should be the join you're looking for.
----- Original Message ----- From: "Ray Champagne" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Monday, December 20, 2004 12:21 PM Subject: Re: database query > Yea, it doesn't like that either. From my Googling, I have found that > Access doesn't like the join clause syntax (duh) but I have yet to find > the > solution. > > Ray > > At 01:16 PM 12/20/2004, you wrote: >>Try: >>Select company_ID, company_name, contact_company_FK, contact_name >> From companies, Contacts >>Where company_ID(+) = contact_company_FK >> >> >> >>On Mon, 20 Dec 2004 12:37:24 -0500, Ray Champagne <[EMAIL PROTECTED]> wrote: >> > Yea, that is what I did, but keep getting an error saying "Join not >> supported" >> > >> > Here is a copy of the error message I get: >> > >> > Error Executing Database Query. >> > >> > [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC >> > Microsoft >> > Access Driver] Join expression not supported. >> > >> > The error occurred in >> > D:\CFusionMX\wwwroot\Data\CV_ADMIN\pages\view.cfm: >> > line 13 >> > >> > Maybe this is specific to Access? >> > >> > Ray >> > >> > At 12:35 PM 12/20/2004, you wrote: >> > >Select company_ID, company_name, contact_company_FK, contact_name >> > > From companies LEFT OUTER JOIN >> > >Contacts ON company_ID = contact_company_FK >> > > >> > >Or RIGHT >> > > >> > >-e >> > > >> > >-----Original Message----- >> > >From: Ray Champagne [mailto:[EMAIL PROTECTED] >> > >Sent: Monday, December 20, 2004 12:23 PM >> > >To: CF-Talk >> > >Subject: database query >> > > >> > >I have two tables in my DB: >> > > >> > >Companies: >> > > >> > >company_ID (PK) >> > >company_name >> > >etc >> > > >> > >Contacts: >> > > >> > >contact_company_FK (FK linked to company_ID) >> > >contact_name >> > >etc >> > > >> > >What I need to do is query the DB and get all the companies and all >> > >the >> > >contacts associated with that company, even if there are no contacts >> > >for a >> > >particular company. I am passing in the #company_ID# to the page. I >> think >> > >what I am supposed to be doing is a LEFT JOIN, but I can't get the >> > >syntax >> > >to work correctly. >> > > >> > >Anybody got any ideas for this? I am using the evil MS Access DB and >> > >CFMX >> > >6.1. >> > > >> > >Thanks! >> > > >> > >Ray >> > > >> > > >> > > >> > >============================================= >> > >Ray Champagne - Senior Application Developer >> > >CrystalVision Web Site Design and Internet Services >> > >603.433.9559 >> > >www.crystalvision.org >> > >============================================= >> > > >> > >The information contained in this transmission (including any attached >> > >files) is CONFIDENTIAL and is intended only for the person(s) named >> > >above. If you received this transmission in error, please delete it >> > >from your system and notify us immediately. If you are not an intended >> > >recipient, please note that any use or dissemination of the >> > >information >> > >contained in this transmission (including any attached files) and the >> > >copying, printing, or retransmission of that information is strictly >> > >prohibited. You can notify us by return email or by phone at >> > >603.433.9559. >> > >Thank you. >> > > >> > > >> > > >> > > >> > > >> > > >> > >> > >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188314 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
