I think I am a bit confused as to what your scenario is... Is 5 the minimum a customer can purchase?
If they order 5 or more, is $12 their price, or if they order 1-5 it is $12? If the pricing is for 1-5 = $12 each, then I think you can adjust your table a little to get the proper results. Add a row, 1, 12.00 Change 5, 12 to 6, 11.55 And so on. Then you can use your example query to pull the correct data. Also, the 'price' should be a number (without the $ sign). It will make it easier to use the number for multiplying with later. William ------------------ William E. Seiter -----Original Message----- From: Mike Little [mailto:[email protected]] Sent: Wednesday, May 05, 2010 5:12 PM To: cf-talk Subject: query for experts? hey guys, hopefully an easy one... tblPricing ---------------------- min_qty (INT) price a product has the following rows... 5, $12.00 10, $11.55 the customer adds a quantity of 4 to their cart. how do i establish that the price should be $12.00? existing query would be... SELECT price FROM tblPricing WHERE product_min <= cart.qty ORDER BY product_min DESC LIMIT 1; doesn't work in this case. mike ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333375 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
