MySQL - get value from another table if column is zero

Question

Let's say I have a table setup with several values, including a name, an identifier, and a foreign key that references the identifier of another table. The name may be null. When I select all the records from this table, I want to get the name if it is not equal to zero. If so, I want to get the name of the record referenced by the foreign key. I can change the database structure if necessary, or just change the query. What are my options?

Answer 1

Use IFNULLor COALESCE:

SELECT T1.ID, IFNULL(T1.name, T2.name) AS name
FROM firsttable T1
LEFT JOIN secondtable T2
ON T1.T2_id = T2.id

Answer 2

Use ISNULLforsql

SELECT T1.ID, ISNULL(T1.name, T2.name) AS name
FROM firsttable T1
LEFT JOIN secondtable T2
ON T1.T2_id = T2.id