Sql query to get this result

Question

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

I need to select homePhone for each user as the first pref
if there is no value I will go for mobilePhone and
if there is no value for him
I will go for workPhone ....

Any suggestion how to do this in mysql ..

Answer 1

Try using Sql Server COALESCE (Transact-SQL) ,

Returns the first non-zero expression among its arguments.

The same goes for MySql COALESCE (value, ...)

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

Sort of

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

Answer 2

You want the Coalesce function to return the first nonzero value:

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

Coalesce exists in MySQL. This is a specific ANSI function.

Coalesce Function (MySQL).

Answer 3

 SELECT user, homePhone FROM user WHERE homePhone != '' UNION SELECT user, mobilePhone FROM user WHERE homePhone = '' AND mobilePhone != '' UNION SELECT user, workPhone FROM user WHERE homePhone = '' AND mobilePhone = '' 

Answer 4

You can use nested IF or CASE IF or perform this task using the business logic level

Answer 5

Another solution is to use the following SQL:

 ISNULL(ISNULL(HomePhone,MobilePhone),WorkPhone) AS Phone 

Thanks Nirmal