Page 1 of 1

SELECT products BETWEEN price range

Posted: Thu Aug 25, 2005 9:43 am
by kendall
Hello,

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.24
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

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 , 30
to bring up only products whose minimum qty of its first price option falls within the price range
problem is
most 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.
I want to be able to bring products whose min qty of its first price option falls within the defined price range
example of the code in effect is at here
KEndall
Im frustrated
:?

Posted: Thu Aug 25, 2005 9:51 am
by nielsene
I think you're going to need a subselect here. Something like

Code: Select all

SELECT DISTINCT ProductID, PriceOption, QTY, Price 
  FROM `product_prices` 
WHERE Price BETWEEN 3.01 AND 7.00
 AND PriceOption ='1 Colour Print' 
 AND QTY IN (SELECT min(QTY) FROM `products_prices` GROUP BY ProductId);
Hmm maybe not... It might work, but I'm starting to think not... but I don't have a DBMS available at present to test on...

Posted: Thu Aug 25, 2005 9:53 am
by kendall
Hmmm,

i will give it a shot....a sub select i dont know of that sort of thing

im currently experimenting with the GROUP BY MIN feature

Code: Select all

SELECT DISTINCTROW ProductID, PriceOption, QTY, Price
FROM `product_prices`
GROUP BY ProductID
HAVING MIN( QTY )
AND Price
BETWEEN 3.01
AND 7.00
AND PriceOption = '1 Colour Print'
ORDER BY Price, QTY ASC

Posted: Thu Aug 25, 2005 10:01 am
by nielsene
Yes, HAVING should work, but it would be more like:

Code: Select all

SELECT DISTINCTROW ProductID, PriceOption, MIN(QTY), Price
FROM `product_prices`
GROUP BY ProductID
HAVING price
BETWEEN 3.01
AND 7.00
AND PriceOption = '1 Colour Print'
ORDER BY Price, QTY ASC

SELECT products BETWEEN price range

Posted: Thu Aug 25, 2005 10:06 am
by kendall
Hmmmm,

ok thanks....i think i got mines to work though but i will try your option...why would that be more effect though? more effcient? more accurate?

you can see the result of my version at here


feyd | switched [url] to a labeled one, string too long.`

SELECT products BETWEEN price range

Posted: Fri Aug 26, 2005 2:56 pm
by kendall
Guys, i have a bigger problem now

it seems that my code would not work after all

I now have products whose price options that are different from 'Price Options' (see original post)

so i cant
AND PriceOption = '1 Colour Print'
Im completely stumped :(