CODEWITHSUNDEEP
mysqlselect
MOLEDesign
MOLEDesign

Reputation: 488

MYSQL Return a value if exists, if not return another

I have a simple table (items) which has two fields in particular: group_name and single_name

Only a handful have a value for group_name but I need to return one or the other as display_name.

If group_name exists then that is returned (i.e group_name as display_name), if group_name is NULL then return single_name as display name.

I'm sure I could do lots of nested select statements, but I'm sure MYSQL has a much easier way of doing this using an if statement. i.e in PHP code I would do something like this:

if (group_name IS null) {
    display_name = single_name
} else {
    display_name = group_name
}

I will be rendering the data in Angular so would prefer the SQL / API server did do the work rather than the client at render time.

Upvotes: 2

Views: 3697

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use IFNULL to solve this:

SELECT IFNULL(group_name, single_name) AS display_name FROM items

... for the completeness you can also use CASE:

SELECT CASE WHEN group_name IS NULL THEN single_name ELSE group_name END AS display_name FROM items

... or you can use IF:

SELECT IF(group_name IS NULL, single_name, group_name) AS display_name FROM items

demo: http://sqlfiddle.com/#!9/853182/3

Upvotes: 3

Raymond Nijland
Raymond Nijland

Reputation: 11602

You can also use IF

SELECT 
 IF(group_name IS NULL, single_name, group_name) AS display_name 
FROM 
 [table]

Or CASE 

SELECT 
 CASE 
  WHEN group_name IS NULL 
  THEN single_name 
  ELSE group_name
 END 
  AS display_name
FROM 
 [table]

Upvotes: 3

Related Questions