CODEWITHSUNDEEP
mysqlselect
bala3569
bala3569

Reputation: 11010

Sql query to get this result

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

Upvotes: 4

Views: 144

Answers (5)

Nirmal Singh Raja Reegan
Nirmal Singh Raja Reegan

Reputation: 377

Another solution is to use the following SQL:

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

Thanks, Nirmal

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157895

You can use nested IF statements, or CASE or do this job using business logic layer

Upvotes: 0

eric.christensen
eric.christensen

Reputation: 3231

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 = ''

Upvotes: 0

Thomas
Thomas

Reputation: 64645

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

Upvotes: 3

Adriaan Stander
Adriaan Stander

Reputation: 166396

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

Upvotes: 7

Related Questions