SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Best approach to build a filtering Query?

    Hi!

    I'm trying to build a query to filter car makes, models and trims based on price ranges.

    My table name is "TblVersoes" and my price field is called "Euros"
    So, I have 4 price ranges:

    1 -> TblVersoes.Euros >= 15000 AND TblVersoes.Euros < 25000
    2 -> TblVersoes.Euros >= 25000 AND TblVersoes.Euros < 35000
    3 -> TblVersoes.Euros >= 35000 AND TblVersoes.Euros < 45000
    4 -> TblVersoes.Euros >= 45000

    The user will be able to select 1 or more prices.
    For just one price it's easy.
    But what if the user selects price range 1 and 4 or 2 and 3?

    How should I build the query for those cases?

    My DB is MSSQL and I'm building my webpage with PHP.

    Thankx for any help,
    Mário
    Yours truely
    Mário Ramos

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by mjpr View Post
    But what if the user selects price range 1 and 4 or 2 and 3?
    are you really sure you want to offer this choice to users?

    please explain why someone would be interested in a car that is between 15000 and 25000 (range 1) or greater than 45000 (range 4) but not between 25000 and 45000

    that doesn't make sense

    i think you should allow a choice of only one price range
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi r937.
    You have a good point. I could limit the price choice to only 1 range at a time.
    Maybe selecting range 1 and 4 is really silly (it's just another way of saying any price at all), but range 1 and 2, 2 and 3 make as much sense as only range 1 or only range 4, for example.

    Thanks
    Yours truely
    Mário Ramos

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    in my opinion, your ranges should be inclusive

    1 -> up to 25000
    2 -> up to 35000
    3 -> up to 45000
    4 -> over 45000
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That would resolve some issues. The user could only select 1 price range with that approach.
    Yours truely
    Mário Ramos

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    I agree with r937.

    However, just to answer the question as a learning exercise.

    But what if the user selects price range 1 and 4 or 2 and 3?
    If they want 1 and 4, it means it should be valid if the range is within 1 or 4.

    1 OR 4
    (1) OR (4)
    (TblVersoes.Euros >= 15000 AND TblVersoes.Euros < 25000) OR (TblVersoes.Euros >= 45000)

    See what I did there?

    (Just to note, the last set of parentheses isn't really necessary, it's just there for clarity.)

    Also, note that if it was filtered so they were AND-ed together (which you could technically do), they would never return a result, as no number can be >= 45000 and less than 25000.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by samanime View Post
    Just to note, the last set of parentheses isn't really necessary
    neither is the first set of parentheses
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Quite true. =p

  9. #9
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your input guys!
    Yours truely
    Mário Ramos


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
  •