SELECT products BETWEEN price range
Posted: Thu Aug 25, 2005 9:43 am
Hello,
i have the following table that has a product with several different prices based on price option and quantitiy
Now i have a combo box that allows visitors to view products by a defined price ranges (e.g 1.00 - 2.00)
then i use this code
to bring up only products whose minimum qty of its first price option falls within the price range
problem is
example of the code in effect is at here
KEndall
Im frustrated

i have the following table that has a product with several different prices based on price option and quantitiy
Code: Select all
ID ProductID PriceOption QTY Price
1 PID3 1 Colour print 10 1.25
2 PID3 1 Colour print 20 1.00
3 PID3 2 Colour print 10 4.25
4 PID3 2 Colour print 20 1.50
5 PID6 1 Colour print 50 1.15
6 PID6 1 Colour print 60 1.00
7 PID6 1 Colour print 100 0.75
8 PID6 2 Colour print 50 2.15
9 PID6 2 Colour print 60 0.75
10 PID6 2 Colour print 70 0.24
13 PID7 Full Colour print 50 12.46
14 PID7 Full Colour print 100 8.24
15 PID8 Large 10 123.24
16 PID8 Large 20 100.00
17 PID8 Medium 10 84.10
18 PID8 Sml 50 90.24then i use this code
Code: Select all
example of the existing code can be found at
SELECT DISTINCTROW ProductID, PriceOption, MIN(QTY), Price
FROM `product_prices`
WHERE Price
BETWEEN 3.01
AND 7.00
AND PriceOption = '1 Colour Print'
GROUP BY ProductID
LIMIT 0 , 30problem is
I want to be able to bring products whose min qty of its first price option falls within the defined price rangemost products have more than one pricing
every products' min qty is different
i keep getting products whose other price options and qty falling in the price range when they should not.
example of the code in effect is at here
KEndall
Im frustrated