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?
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]..."
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 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.