Handling IF condtions in MySQL

Question

I have a query that based on various ids within a table should return different results.. So for example:

Table 'orders'

• product_id
• item_id
• extra_id
• something_id

I'd like return a result based on the values of these, such as

IF item_id != 0 'ITEM' ELESEIF product_id != 0 'PRODUCT', etc.

Does anyone know how to do this?

Thanks!

Answer 1

SELECT IF (item_id <> 0,'ITEM',IF (product_id <> 0, 'PRODUCT','N/A')) as type

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Answer 2

The CASE expression is ANSI, supported on numerous databases:

SELECT CASE 
         WHEN o.item_id != 0 THEN 'ITEM'
         WHEN o.product_id != 0 THEN 'PRODUCT'
       END AS your_column_name
  FROM ORDERS o

ANSI is preferred for the portability, rather than native vendor syntax where possible.