How to use if else statement in sql with comparing 3 data

Question

I would like to ask about how to create a select query that compare 3 value. Such as I got 3 data which is mobile, home and office. I want to display one row only for example if my mobile is a null value, it will display home, and if my mobile and home is null, it will display office. I try it with if else code, but only work when mobile is null, it will display home. But I can't get the result of office when mobile and home is null.

SELECT  CASE    WHEN client_mobile IS NOT NULL THEN client_mobile
            WHEN client_mobile IS NULL THEN client_home
            WHEN client_mobile IS NULL AND client_home IS NULL THEN client_office
            ELSE 0
           END
AS client_contact FROM contact; 

I would like to get the output of

111111

444444

Answer 1

coalesce should do exactly that - it returns the first non-null argument it's given (or null, if they're all null, of course).

SELECT COALESCE(client_mobile, client_home, client_office) AS client_contact
FROM   contact