This is the answer. Many thanks. James Brown
----- Original Message ----- From: "Cantrell, Adam" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003 4:41 PM Subject: RE: SQL Multiple Reference Tables Question > This may work, you'll want to look into using cfqueryparam around your > passed variables to avoid SQL injection attacks. You will also want to play > with the WHERE contraint to fit your needs - you may want records that match > all criteria (AND) or you may want records that match any of them (OR) > > Use LEFT OUTER JOIN, if you want to pull records that don't necessarily have > a reference. > > > <CFQUERY name="thinglist" datasource="mydata"> > SELECT > tblThing.ThingKey, > tblThing.ThingName, > tblColor.ColorDescription, > tblCategory.CatDescription, > tblMaker.MakerName > FROM > tblThing LEFT JOIN tblColor ON tblThing.ColorKey = tblColor.ColorKey > LEFT JOIN tblCategory ON tblThing.CatKey = tblCategory.CatKey > LEFT JOIN tblMaker ON tblThing.MakerKey = tblMaker.MakerKey > WHERE > tblColor.ColorKey = #url.colorKey# > AND tblCategory.CatKey = #url.catKey# > AND tblMaker.MakerKey = #url.MakerKey# > </CFQUERY> > > > Adam. > > > > > -----Original Message----- > > From: James Brown [mailto:[EMAIL PROTECTED] > > Sent: Friday, February 28, 2003 3:28 PM > > To: CF-Talk > > Subject: SQL Multiple Reference Tables Question > > > > > > This is probably basic, since I am new at this, but I want to > > know what is > > "best." I have simplified the tables for illustration. > > > > I have one "main table" > > > > tblTHING > > ThingKey Name ColorKey CatKey > > MakerKey > > -------------- ------------------- ---------------------- > > ----------- > > ----------- --------------------- > > 001 WinterSap 1 > > 2 > > 6 > > 002 HorsePlay 1 > > 4 > > 5 > > 003 HouseBarn 3 > > 1 > > 4 > > > > and three "reference tables." The purpose of each is to > > supply a lookup > > value to the tblThing, the main table. The foreign keys in > > the reference > > tables have the same field name as the corresponding fields > > in the main > > table. > > > > tblCOLOR > > ColorKey ColorDescription > > ------------------ ---------------------- > > 1 Blue > > 2 Yellow > > 3 Purple > > 4 Red > > > > tblCATEGORY > > CatKey CatDescription > > ---------------- --------------------- > > 1 Animal > > 2 Activity > > 3 Human > > 4 Dwelling > > 5 Season > > 6 Biological Substance > > > > > > tblMAKER > > MakerKey MakerName > > ---------------- -------------------- > > 1 Mary > > 2 Joe > > 3 Willy > > 4 Mike > > 5 Roman > > 6 Jameson > > 7 Amanda > > > > I want a query that produces the following result: > > > > ThingKey Name Color Category > > Maker > > -------------- ------------------- ---------------------- > > ----------- > > ----------- --------------------- > > 001 WinterSap Blue > > Activity > > Jameson > > 002 HorsePlay Blue > > Dwelling > > Roman > > 003 HouseBarn Purple Animal > > Mike > > > > The values in the Color, Category and Maker columns have, of > > course, been > > supplied from the reference tables. > > > > Which I CAN do with the following Query: > > > > <CFQUERY name="thinglist" datasource="mydata"> > > SELECT > > tblThing.ThingKey, > > tblThing.ThingName, > > tblColor.ColorDescription, > > tblCategory.CatDescription, > > tblMaker.MakerName > > > > FROM > > tblThing, > > tblColor, > > tblCategory, > > tblMaker > > > > WHERE > > tblColor.ColorKey = tblThing.ColorKey > > AND tblCategory.CatKey = tblThing.CatKey > > AND tblMaker.MakerKey = tblThing.MakerKey > > > > </CFQUERY> > > > > ============================================================= > > It seems like there should be a better way to structure the query, > > particularly if I want to supply a variable that will display ONLY the > > records where the maker, category, and Color (one, all or > > none) meet certain > > conditions. (i.e. list all the Red things made by Amanda) > > > > I have tried using a JOIN, which works very well if I Join only one > > reference table, but does not seem to work for more than one. > > > > Is there a way to relate the reference tables in the SELECT > > part of the > > query? > > > > Thanks for any help > > > > James Brown > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
