You're probably wanting outer joins -- (syntax is different for Oracle) select blah blah blah from tblThing left join tblMaker on ( tblMaker.makerid = tblThing.makerid ) left join tblColor on ( tblColor.colorid = tblThing.thingid )
This will return all the items in the tblThing table with 0 or more associations to the other tables -- so if you wanted to filter out only red things made by whomever, then you would add a where clause like where tblmaker.makerid = x and tblcolor.colorid = x hth s. isaac dealey 954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 > 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 > Get the mailserver that powers this list at > http://www.coolfusion.com > Unsubscribe: http://www.houseoffusion.com/cf_lists/uns > ubscribe.cfm?user=633.558.4 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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
