mysql conditional query - complicated!

Question

i want to get distinct values for a field, let say: field1... ok this needs a query like: "select distint(field1) from table"

however for some records, field1 is empty and there is another column that is an alternative to field1, which is field2. now; for the records where field1 is empty i need to use the value of field2. i think i need sort of a conditional select statement with if control something like:

"select distinct( (if(field1!='') field1 else field2) ) from table"

but i have no idea on how to write it. any help is appricated...

Answer 1

SELECT DISTINCT IFNULL(field1,field2) FROM table

should do the trick.

Answer 2

i think i did it (not sure if the result is correct):

SELECT DISTINCT (
 IF( field1 <> '', field1, field2)
)
FROM table

Answer 3

SELECT DISTINCT (
 IF( coalesce(field1,'') <> '', field1, field2)
)
FROM table

This would work for both null and empty field1.