How to do this triple 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 run two different queries and check 3 fields to determine the $ p_price variable in PHP. I want to condense this in one query, conditions: 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 should I do with the second part? Should I use a case? I need the most efficient solution in terms of execution speed, because it is part of an extremely large request.

EDIT:

Since it seems that some of you have not understood, take this as an alternative.

IF(IFNULL(field1,field2)>0,IFNULL(field1,field2),field3) The above MySQL query condition works with the above logic, but as you can see, it double-evaluates field1 and field2 to check for NULL, which, in my opinion opinion is not very efficient, so I'm looking for the best query / condition to rewrite the same.

Answer 1

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

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

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

It corresponds:

• If field1 is not NULL, use field1, otherwise ...
• If field2 is nonzero, use field2, otherwise ...
• Use Field3

Answer 2

you can use as below

 lets Try this : select if(Id is null, 0, id) as Id; 

As well as mysql providing a very good document for the conditional statement

Also, if you can use another case, for example

 SELECT USER_ID, (CASE USER_ID WHEN 1 THEN 1 ELSE 0 END) as FIRST_USER FROM USER 

Let me know if I can help you further.

Answer 3

You can use ISNULL () or NOT () in Mysql.

Answer 4

The standard SQL COALESCE function looks like you need:

COALESCE (a, b, c, d, "banana") means that the expression will take the first non-zero value that it encounters, working from left to right. It is quite efficient and takes only a few processor cycles.

It can take as many parameters as you need, from two to a gazillion.