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
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
