Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When using SELECT can you modify the value of a returned field based on other fields?

Tags:

sql

mysql

When using MySQL SELECT can you change the value of a returned field based on other fields?

For example, if I have this select:

SELECT city,state,country FROM table

Now if city is empty AND state is empty, I want the value of country to returned empty as well (whether country actually has a value or not).

Example table:

id | city | state | country
-----------------------------
1  | Here | There | MyCountry
2  |      |       | YourCountry

So with the above table, I want the results for id=1 to return Here,There,MyCountry but the results for id=2 should be empty,empty,empty

Thanks

EDIT: To clarify, a WHERE clause will not work because I need the row returned even if city and state are empty. A better example might have been SELECT id,city,state,country FROM my_table

like image 230
jsherk Avatar asked Jan 15 '11 18:01

jsherk


1 Answers

Update(misprints corrected):

SELECT city,state,
CASE 
 WHEN (city IS NULL OR city='') AND (state IS NULL or state='') THEN ''
 ELSE country
END as country_1
 FROM `table`

You can also use IF instead of CASE:
IF ((city IS NULL OR city='') AND (state IS NULL or state=''),'',country) as country_1

like image 132
a1ex07 Avatar answered Sep 28 '22 04:09

a1ex07