SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    At My Desk!!
    Posts
    1,642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query for Some price banding

    Hiya Guys,

    I can't quite get my head around the SQL I need to use. Consider this; I have a table with the following:

    Id
    LowValue
    HighValue
    Price

    The idea is that the user is able to set up some price banding so something like:

    1 or 2 = 25
    3 or 4 = 35 ect ...

    The SQL I am using is working fine, as long as the number that has been input is in either the ValueLow or the ValueHigh. Here is the SQL:
    PHP Code:
    SELECT banPrice FROM AJ_Site_Price_Banding WHERE $people BETWEEN banValueLow AND banValueHigh 
    So, using the above example, if I enter the number 5, as its not in the DB it will come back with the lowest value, is there any way to structure the query so that it comes back with the highest one?

    p.s I could do this using PHP and then run a couple of queries to make sure that value is in the DB first and then structure my second query but I think it should be possible in one query

    Thanks in advance
    "Am I the only one doing ASP.NET in Delphi(Pascal)?"

  2. #2
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webnoob View Post
    So, using the above example, if I enter the number 5, as its not in the DB it will come back with the lowest value, is there any way to structure the query so that it comes back with the highest one?
    From your query it seems like it should return no value at all. Could you paste the data you're running query on, and the exact query that gets generated?

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    At My Desk!!
    Posts
    1,642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have thought of a way round for this, I will just have a price banding that goes like:

    5 to 99999 = 45

    This will solve the problem (was actually my original intention but I had forgotten(amazing what happens when you read your own tech spec ))

    Would be interested in seeing a query that could have done it though if someone has any time and ideas.

    Thanks again
    "Am I the only one doing ASP.NET in Delphi(Pascal)?"

  4. #4
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    At My Desk!!
    Posts
    1,642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by earl-grey View Post
    From your query it seems like it should return no value at all. Could you paste the data you're running query on, and the exact query that gets generated?

    Yes, sorry that was my mistake. It's not returning a value

    p,s I think my week old baby is making me loose to much sleep, getting everything mixed up
    "Am I the only one doing ASP.NET in Delphi(Pascal)?"

  5. #5
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In case intervals don't overlap and there are no gaps:
    Code MySQL:
    SELECT banPrice
      FROM AJ_Site_Price_Banding 
     WHERE banValueLow <= $people
    ORDER
        BY banValueLow DESC
     LIMIT 1


Bookmarks

Posting Permissions

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