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/unsubscribe.cfm?user=89.70.4
