SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: mysql query

  1. #1
    ********* Ornithologist AtomicPenguin's Avatar
    Join Date
    May 2002
    Location
    Vancouver, BC
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql query

    Hey everyone. Here's my prob: I have a page which, depending on choices the visitor makes, queries a database to return all values from a MySQL database which meet those criteria. So far so good. My problem is, one choice they're allowed to search for is a number (a price, actually). Now, the db contains TWO price fields. THe first is the initial price of the thing and the second is the REDUCED price of the thing.

    If the item hasn't been reduced, its reduced price has a value NULL. So, my problem is: how do I structure my query to search for items between a certain price range, since it would have to analyze different fields (price / reduced price) depending on whether reduced price is NULL or not.

    Am I being clear? ...! Can't think a way to do this one... BADLY designed db.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    where coalesce(reducedprice,regularprice)
    between lowprice and highprice

    coalesce is a standard sql function that returns the first non-null value in a list

    in your case, if reducedprice is null, it returns regularprice

    just what the doctor ordered, and a pretty decent db design, if you ask me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •