I'm trying to write a query that basically is searching by price, though sometimes the items may have a discount on them and this price isn't stored in the DB, only the discount percent and dates the discount runs between are, so we have to work out the price of the item on the fly to see if it should be included.

Additionally, if the item is an auction we ignore the sale price and just look at the start_price and max_bid to work out the current price (max bid is only set if the item has at least 1 bid).

We know it is NOT an auction if start_price == buyout_price, so know we have to figure out if the item is on sale or not and if so calculate the sale price to see if to include it in their requested price range.

I have this code..

    auctions AS a
    IF(a.start_price == a.buyout_price AND a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_end_date > UNIX_TIMESTAMP() AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) >= '500' AND IF(a.start_price == a.buyout_price AND UNIX_TIMESTAMP() > a.discount_start_date AND UNIX_TIMESTAMP() < a.discount_end_date AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) <= '700'

As you may be able to make out I'm trying to get all results with prices between 500 - 700.

I'm also getting a error which appears to have something to do with the UNIX_TIMESTAMP(), though not sure why!?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== a.buyout_price AND  a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_e' at line 1
  • 3
    The equality operator is = in SQL (not ==). – ypercubeᵀᴹ Feb 19 '14 at 16:27
  • But I'd really prefer that long IF(...) to be rewritten with AND and OR – ypercubeᵀᴹ Feb 19 '14 at 16:28
  • 2
    @ypercube is expressing more than just a preference, incidentally... using IF() in the WHERE clause is going to defeat a lot of potential optimizations that might have used indexes to eliminate a large portion of rows. The same thing applies to CASE or any other expression that involves columns as arguments and makes things in the WHERE clause non-sargable, forcing the server to evaluate that expression for every row in the underlying tables that can't be eliminated by another means. – Michael - sqlbot Feb 19 '14 at 16:35
  • @ypercube ahhhh.... good catch haha.... – Brett Feb 19 '14 at 16:36
  • @Michael-sqlbot Hmmm thanks. I will see if I can rewrite it, that is unless anyone provides one in an answer :P – Brett Feb 19 '14 at 16:38

Write yourself a view that produces, in a single column, the value of price that you're interested in. It might be a UNION of a few different criteria. When you're done you'll have a view of all items with two columns: the id and the price.

Join that view to whatever else you need (perhaps including the original price), and voila.

Tip: skip IF and just use AND and OR, per the SQL standard. And eschew LEFT JOIN unless you know -- and know why -- you can't use a proper inner join. If the database is pervasively unreliable in that regard, it will lead to more pain than all the outer joins in the world can alleviate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.