Hi,
I've the following code that generates some cfqueries but I want to avoid the
loop and have it all delat with in the one query. Anyone know how to go about
this?
Here's the code I've so far:
<!--- loop the list --->
<cfloop list="productIDs" index="i">
<!--- Get the new maximum discount for this order --->
<cfquery name="qGetMaxDisc" datasource="#variables.DSN#">
SELECT max(PR.discount) as theDiscount
,PR.productID1 as pr1
,PR.productID2 as pr2
FROM productRelate as PR
LEFT OUTER JOIN
products as pp
ON (PR.productID1 = PP.productID
OR PR.productID2 = PP.productID)
AND (PR.productID1 = <cfqueryparam value="#i#"
cfsqltype="cf_sql_integer" />
OR PR.productID2 = <cfqueryparam value="#i#"
cfsqltype="cf_sql_integer" />)
WHERE PP.productID IN (<cfqueryparam value="#productIDs#"
cfsqltype="cf_sql_integer" list="true" />)
AND PP.productID <> '<cfqueryparam value="#i#"
cfsqltype="cf_sql_integer" />
AND PP.active = '1'
GROUP BY
PR.productID1
,PR.productID2
</cfquery>
Thanks,
Richard
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284185
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
