Brad Wood wrote:
> If you query is highly dynamic, this way may not be best. Let's say the
> user can specify groups that are of type college, jewish, preschool,
> catholic, and skinny. Now, you have to join to the type table 5 times
> and that ain't looking too good.
Joining is what databases are good at. Only I would rewrite the query a
bit to only have one repeating part and keep it readable:
<cfset grouptypes = 'college,jewish,preschool,catholic,skinny'>
<cfset i = 1>
<cfquery>
SELECT
x.*
FROM
(
SELECT *
FROM table
WHERE table.y = <cfqueryparam value="#ListFirst(grouptypes)#">
) x
<cfloop list="#ListRest(grouptypes)#" index="currentType">
<cfset i++>
INNER JOIN
(
SELECT id
FROM table
WHERE table.y = <cfqueryparam value="#currentType#">
) y#i# ON x.id = y#i#.id
</cfloop>
</cfquery>
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300433
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
