Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2006
    Posts
    4

    Question Unanswered: Product Price selection through Mysql query

    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
    Last edited by bluesapphire; 10-07-06 at 10:10.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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]..."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    4
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2006
    Posts
    4
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let me ask you -- what do you think you should do in that case?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2006
    Posts
    4
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •