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).

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 statements, or CASE or do this job using business logic layer

Answer 5

Another solution is to use the following SQL:

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

Thanks, Nirmal