James, I'm not too sure about that LEFT join.
I mean, if you were to add a row to tblThings that contained a NULL value for any or all of the foreign keys, (if that's possible with your schema), the LEFT join would return the row with the NULL(s) in addition to all the rows that have matching key values. But maybe that's what you want. Anyway, I think it's worth noting that the INNER (or NATURAL) join will *also* allow you to filter out rows based on conditions in your WHERE clause, like ColorDescription = 'Red.' ~Dina ----- Original Message ----- From: "James Brown" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003 4:30 PM Subject: Re: SQL Multiple Reference Tables Question - Thanks > 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
