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