Re: tricky sql to avoid loop

[Richard Cooper]

Thanks for the reply dominic, looks like I may now have the solution using a 
union.
cheers,

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:284327
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: tricky sql to avoid loop

[Dominic Watson]

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)
ANDPP.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
FROMproductRelate 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