Thank you for your response, i ended up using an iif statment in the end, which gives the same results as your solution, please see below
AND (((iif(products.cfsalePrice=0, products.rrPrice,products.cfsalePrice))<=1000)) Thanks >I haven't tested, but maybe something like this? > >SELECT productType.*, productRange.*, products.*, > Displayprice = CASE products.cfsalePrice > WHEN products.cfsalePrice = 0 THEN products.rrPrice > ELSE products.cfsalePrice > END >FROM (productType, productRange, products >WHERE productType.prodtypeID = productRange.prpdType >AND productType.prodtypeID = products.pdType >AND productRange.prID = products.pdRange) >AND Displayprice <= 1000 > >William >-- >William E. Seiter > >Have you ever read a book that changed your life? >Go to: www.winninginthemargins.com >Enter passkey: goldengrove > >Web Developer / ColdFusion Programmer >http://William.Seiter.com >Hi > >I am working on a product database, and need to drill down the products >using price bands i.e. upto £1000, £1001 to £2500 etc. > >My problem is; the database table has two fields for pricing info, an RRP >and a sale price. The RRP price has to stay intact and can not be changed to >the sale price. (If the product is not on offer the sale price field remains >at £0.00.) If i use the following clause i get all the products, quite >rightly as the sale price field is less than 1000 > >AND (((products.rrPrice)<=1000)) OR (((products.cfsalePrice)<=1000)) > >so i added this > >AND (((products.cfsalePrice) > 0)) > >This now returns all products with an RRP less than 1000, great!! However, >this will not return a product with a sale price of £800, and a RRP of >£1250, which is right!! Because the RRP is greater than 1000, but i need it >to return the on sale products as well, regardless that the RRP is still >higher than 1000. I hope this make sense. Any help would be appreciated. > > >Full query below; > >cfquery name="getProducts" datasource="#application.dsn#"> >SELECT productType.*, productRange.*, products.* >FROM (productType INNER JOIN productRange ON productType.prodtypeID = >productRange.prpdType) INNER JOIN products ON (productType.prodtypeID = >products.pdType) AND (productRange.prID = products.pdRange) >WHERE 0=0 AND (products.pdArchive) = 0 >AND (((products.rrPrice)<=1000)) OR (((products.cfsalePrice)<=1000)) >AND (((products.cfsalePrice) > 0)) ></cfquery> > >Jason ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:298700 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
