Merging columns using left join in SQL

March 17, 2022

I am trying to merge columns from three different tables, the tables are as follows:

ID     Name      Location1  
1      John      Ams
2      Elvis     DH 
3      Stuart    NY
4      Tim       CAL

ID     Name      Location2    
1      John      ROT
2      Elvis     FT

ID     Name      Location3 
1      John      HOL

Desired output should be such that inner join is based on ID, a column is created named "Last location" that equals Table3.Location3 if filled in, otherwise Table2.Location2 if filled in, otherwise Table1.Location1. So desired output should be:

ID     Name      Last location
1      John      HOL
2      Elvis     FT
3      Stuart    NY
4      Tim       CAL

I am currently using a left join but I do not know how to override values, so I am getting all three columns:

SELECT * 
FROM (Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID) LEFT JOIN Table 3 ON Table2.ID = Table3.ID;

Hence this gives:

ID     Name      Location1     Location2     Location3
1      John      Ams           ROT           HOL
2      Elvis     DH            FT
3      Stuart    NY
4      Tim       CAL

Any help would be appreciated.

>Solution :

Depending on your RDBMS

SELECT 
  Table1.ID, 
  COALESCE(Table3.Location3, Table2.Location2, Table1.Location1) AS LAST_LOCATION
FROM Table1 
LEFT JOIN Table2 
ON Table1.ID = Table2.ID
LEFT JOIN Table 3 
ON Table2.ID = Table3.ID;

SELECT 
  Table1.ID, 
  NVL(Table3.Location3, NVL(Table2.Location2, Table1.Location1))
FROM Table1 
LEFT JOIN Table2 
ON Table1.ID = Table2.ID
LEFT JOIN Table 3 
ON Table2.ID = Table3.ID;