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
-----Original Message-----
From: Jason Congerton [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 10, 2008 9:16 AM
To: CF-Talk
Subject: Drill Down Problem
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:298674
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
