Hi Richard, this bit of SQL is a bit strange:
> WHERE PP.productID IN (<cfqueryparam value="#productIDs#" > cfsqltype="cf_sql_integer" list="true" />) AND PP.productID <> > '<cfqueryparam value="#i#" cfsqltype="cf_sql_integer" /> Lets say your ids were "1,2,3,4,5,6". Translated in one step of the loop you query will look like this: WHERE PP.productID IN (1,2,3,4,5,6) AND PP.productID <> 2 This will produce a load of queries in which one product in the list is ommitted. Is this what you want? I think you just need: WHERE PP.productID IN (1,2,3,4,5,6) And then you don't need to loop either Dominic On 20/07/07, Richard Cooper <[EMAIL PROTECTED]> wrote: > 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 > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284301 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
