Sql query to get this result

Question

Consider i have a user table and I have three columns mobilePhone,homePhone and workPhone...

I have to select homePhone for every user as first pref
if there is no value
   I'll go for mobilePhone and
   if there is no value for it
      I ll go for workPhone....

Any suggestion how it can be done in mysql..

Answer 1

Try using Sql Server COALESCE (Transact-SQL),

Returns the first nonnull expression among its arguments.

Same goes for MySql COALESCE(value,...)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

Something like

SELECT  COALESCE(homePhone, mobilePhone, workPhone) ContactPhone
FROM    Users

Answer 2

You want the Coalesce function which returns the first non-null value:

Select Coalesce(homephone, mobilephone, workphone) Phone
From `user`

Coalesce does exist in MySQL. It is an ANSI defined function.

Coalesce function (MySQL).