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

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

For example, i开发者_JAVA百科f 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

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

The answer is yes; you're looking for what's called control flow functions.

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

a1ex07 is right, but you need to fix a few query errors in his SELECT stmt.

first, there is a missing comma after "state" and second, if your table were really called "table" you have to enclose in backticks 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 second backtick, stackoverflow uses those for syntax highlighting if single.)

Why don't you just use a WHERE clause?

For example:

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

N.B.: You'll need to replace the '<> ""' above with 'IS NOT NULL' if these are actually nulled rather than simply empty.