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

Question

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

For example, if I have this choice:

SELECT city,state,country FROM table 

Now, if the city is empty and the state is empty, I want the country’s value to be empty too (regardless of whether the country matters or not).

Example table:

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

So, with the table above, 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, the WHERE clause will not work, because I need a string returned even if the city and state are empty. A better example would be a SELECT id, city, state, country FROM my_table

Answer 1

Update (typos 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

Answer 2

The answer is yes; You are looking for what are called flow control functions.

Take a look at http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Answer 3

a1ex07 is right, but you need to fix some query errors in its SELECT stmt.

firstly, there is no “comma” after the “state”, and secondly, if your table is really called a “table”, you should wrap it in backward cycles because it is a reserved MySQL keyword.

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

(exlude the second backtick, stackoverflow uses those that are allocated by the syntax if they are single.)

Answer 4

Why don't you just use the WHERE clause?

For example:

 SELECT city, state, country FROM <table> WHERE (city <> "" OR state <> ""); 

NB: You need to replace the '<>' "'above with" IS NOT NULL "if they are actually null and not just empty.