hanznv hanznv - 4 months ago 6
SQL Question

sql basic if else conditions

I have this table:

|car |house|


|2010|2012 |


|2005|2004 |


|2003|2003 |


|2006| |



and I need to create this table from it:

|car |house|result|


|2010|2012 |after |


|2005|2004 |before|


|2003|2003 |after |


|2006| |x |



cases:

car
<
house
result
=before

car
>
house
result
=after

car
=
house
result
=after

house
is empty ->
result
=x

Answer

Use CASE EXPRESSION :

SELECT t.car,t.house,
       CASE WHEN t.house >= t.car THEN 'AFTER'
            WHEN t.house < t.car THEN 'BEFORE'
            ELSE 'x'
       END as result
FROM YourTable t

EDIT: To join this to another table :

SELECT * FROM (
    SELECT t.car,t.house,
           CASE WHEN t.house >= t.car THEN 'AFTER'
                WHEN t.house < t.car THEN 'BEFORE'
                ELSE 'x'
           END as result
    FROM YourTable t) s
JOIN Another_Table tt
 ON(s.result = tt.Column)