table design & query problems ??

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

table design & query problems ??

Post by PHPycho »

Hello forums !!
I am getting trouble in designing and queries some tables.
I am supposed to design a table which contains the prices of the products as per no
and i designed such table as
|id|product_id|qty_from|qty_to|price|
suppose it may contain values like
1| 122 | 1 | 99 | 100 |
2| 122 | 100 | 199 | 80 |
3| 122 | 200 | + | 70 |
In above table for product with id=122 has following values for the given ranges
1 - 99 = $100
100 - 199 = $80
200+ = $70

I dont know whether such table is fit for such or not. and i feel some difficulty in querying.
Case1:
Suppose if i would like to know the price of the product with qty = 250 , how should i perform such query as it lies in the 200+ range ?

Thanks for the suggestions in advance.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: table design & query problems ??

Post by VladSun »

You don't need the qty_from field
[sql] SELECT * FROM products WHERE $qty < qty_to AND product_id  = $id ORDER BY qty_to LIMIT 1[/sql]

And just add as the infinite quantity a very big number :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: table design & query problems ??

Post by Christopher »

You could also check the range:
[sql] SELECT * FROM product_qty_price WHERE product_id = $id  AND $qty >= qty_from AND $qty <= qty_to[/sql]
As VladSun said, you would need to always put some very large number where you currently have '*' (e.g.999999)
(#10850)
Post Reply