Hi!
I have following data in table named "productprice" in mysql.

id |minqty| |maxqty| |price|
1 |10| |20| |10.00 $|
2 |21| |30| |19.00 $|
3 |31| |40| |28.00 $|

My question is that what will be the query to find the price

Case #1 :
If quantity is greater than 40 [maxqty] then it should select record # 3.

Case #2 :
If quantity is less than 10 [minqty] then it should select record # 1 .


I have got hint from a very helpful person that I should use left join along with CASE statement in my query . But my problem is that I am new to database field and haven't have much idea about this left join or CASE statement concept. And due to busyness of that person I don't want to disturb him again for sql query. So can anyone else help me in making sql query using lft join and CASE statement?

Thanks in advance





Kindly give some solution in this regard

Thanks in advance

actually, what i suggested was to add two more rows --

id minqty maxqty price
4 0 9 10.00
5 41 999999999 28.00

this will allow you to use an INNER JOIN based on finding the joined quantity within one of the 5 different ranges

the LEFT OUTER JOIN is necessary only when you do not wish to take the simple solution of adding rows 4 and 5

it needs to be a LEFT OUTER JOIN when joining on your quantity, to cover those instances where the quantity is not found within your original three rows, and you need the CASE expression to find those instances, so that you can determine whether the quantity was too high or too low

take my advice, adding rows 4 and 5 is a lot easier


p.s. your Case #1 should say: "If quantity is greater than 40 [maxqty]..."

Hi!
Extremely thanks for your reply and pleasant surprise to see you here again . The scenario is that all prices would be displayed on admin side of a website. And that person who would use the admin is very raw and he tells me that it creates confusion for him so he wants me to calculate all greater than prices through query [customer is always right]. But I am new to this concept of joining as well as CASE statement. Thats why I am asking for some query help.

[ I have modified your mentioned error.]

Thanks in advance

another approach is to change the table as follows --

id |minqty| |maxqty| |price|
1 |1| |20| |10.00 $|
2 |21| |30| |19.00 $|
3 |31| |999999999| |28.00 $|

simple, yes?

Hi!
I am sorry , I think I cann't explain my problem in in true manner. The problem is that what if the client would add another price of the product Let's say he adds following new record in database :
4 51-60 26.00 $
or add another price in future
5 61-70 25.00 $

I mean that price addition of a product is dynamic and it can be changed by client at any time.

Hope the probelm would be cleared somewhat.

Kind Regards

let me ask you -- what do you think you should do in that case?

I think that , system should dynamically calculate the higest price of quantity and if quantity is out of range that higest price , then higest price should automatically be assigned to that quantity.

Kind Regards

no, i'm sorry, i did not mean that, i should have been more explicit

what do you think you should do if the user submits a change in the ranges?

answer: you should update the table

for example, suppose the user now wants 21-25 to be $19.00, and 26-30 to be $19.50

answer: you have to change the data that's already in the table

okay, so start with this --

id |minqty| |maxqty| |price|
1 |1| |20| |10.00 $|
2 |21| |30| |19.00 $|
3 |31| |999999999| |28.00 $|

and if the user wants any range changed, then you have to change it

get it?