How do I do this ternary operation in mysql?

Question

....
.....
if ($prod_price->RecordCount()>0) {
              $p_price = $prod_price->fields['options_values_price'];
          }
          else
          {
              $p_price = $orders_products_query->fields['products_price'];
          }
          $p_price = ($p_price>0)?$p_price:$orders_products_query->fields['final_price'];
......
..........

As you can guess from the above, I am running two different queries and checking 3 fields to determine the variable $p_price in PHP. I want to condense this in a single query, the conditions being: If field1 is Null, use field2, if field2 is 0, use field 3.

The first part can be solved with IFNULL(field1,field2)....but what do I do about the second part? Should I use case? I need the most efficient solution in terms of execution speed, cause this is part of an extremely large query.

EDIT:

Since it seems the question isn't clear to some of you, take this as an alternative.

IF(IFNULL(field1,field2)>0,IFNULL(field1,field2),field3) The above MySQL query condition works with the above mentioned logic, but as you can see, it evaluates field1 and field2 twice for NULL checking, which I don't believe is very efficient, so I am looking for a better query/condition to rewrite the same thing.

Answer 1

You can use IF(condition, val-if-true, val-if-false) in MySQL.

You can also nest the IF() functions, something like this:

IF(field1 IS NOT NULL, field1, IF(field2 <> 0, field2, field3))

This corresponds to:

• If field1 is NOT NULL, use field1, otherwise...
• If field2 is non-zero, use field2, otherwise...
• Use field3