SQL Multiple Reference Tables Question - Thanks
James, Dina is correct - 'LEFT JOIN' is the same as 'LEFT OUTER JOIN' for most RDBMS's. Some will actually require LEFT OUTER JOIN, but I think that's rare these days. The LEFT/LEFT OUTER is more loose - it's like telling the DB: "give me all the records in the left table, and then if there are any matches in the right table, return a record for those too." To return records that ONLY have a match with the table you're relating, use INNER JOIN, as Dina suggested. This type of join says: "give me only the records from the left table that have a match in the right table" Because you are specifying a contstraint in the where clause, the recordset for each of these would have looked the same in your situation, but it's better form, and probably better performance to use the correct join type in your statement :) Sorry for any confusion. Adam. ~| 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 Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Multiple Reference Tables Question - Thanks
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. > > > > > > > > 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# > > > > > > > > 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 NameColorKeyCatKey > > > 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 > > >
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. > > > > 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# > > > > 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 NameColorKeyCatKey > > 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 ColorCategory > > Maker > > -- --- -- > >--- > > --- - > > 001 WinterSapBlue > > 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: > > > > > > 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 > > > > > > > > = > > 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:
