Ali Ali - 2 months ago 5
MySQL Question

derive value of column from other columns in MySQL

I have a table like this:

----------------
|cloumn1|cloumn2|
-----------------
| 1 | 2 |
-----------------
| 3 | 1 |
-----------------
| 4 | 2 |
-----------------
| 1 | 1 |
-----------------


I need a query from my table with this result:

-------------------------------
|cloumn1|cloumn2|compare_result|
--------------------------------
| 1 | 2 | smaller |
--------------------------------
| 3 | 1 | bigger |
--------------------------------
| 4 | 2 | bigger |
--------------------------------
| 1 | 1 | equal |
--------------------------------


it means if column1 is bigger column 2 the third column(compare_result) shows smaller, if is smaller shows smaller and so on...

Answer

You want to use case:

select t.*,
       (case when column1 > column2 then 'bigger'
             when column1 < column2 then 'smaller'
             when column1 = column2 then 'equal'
             else '???'
        end) as compares_to
from t;

You can wrap this logic in a view, so anyone accessing the table can see it.

The '???' would occur when one or both of the values are NULL.