Search code examples
mysqlsqlcoalesce

MySQL replacing column with another one when null

I'm trying to retrieve data from two joined tables in MySQL.

   table1                   table2
   id                         id - foreign key
   name                    address_old
```other col```            address_new

I would like to get 'name' and 'address_new', or 'name' and 'address_old' if 'address_new' is null.

I've already tried 

ifnull(table2.address_new, tables2.address_old)
CASE WHEN table2.address_new IS NULL 
    THEN table2.address_old 
    ELSE table2.address_new
coalesce(table2.address_new, table2.address_old)

Neither one is working and I keep getting empty results for these.
Can anyone help me with this? Thanks in advance.

Solution

  • you need check both null and empty string

    select t1.name, CASE WHEN table2.address_new IS NULL or  table2.address_new =''
    THEN table2.address_old 
    ELSE table2.address_new address
END as 'Address'
from table1 t1 inner join table2 t2
on t2.id = t1.id