Merging columns using left join in SQL-CodePudding

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.

CodePudding user response：

You can use COALESCE : Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.

Try like below.

SELECT Table1.ID, Table1.Name, COALESCE(Table3.Location3, Table2.Location2, Table1.Location1) AS [Last location]
FROM Table1 
LEFT JOIN Table2 ON Table1.ID = Table2.ID
LEFT JOIN Table3 ON Table1.ID = Table3.ID;

Alternatively you can use ISNULL twice as below.

SELECT Table1.ID, Table1.Name, ISNULL(Table3.Location3, ISNULL(Table2.Location2, Table1.Location1)) AS [Last location]
FROM Table1 
LEFT JOIN Table2 ON Table1.ID = Table2.ID
LEFT JOIN Table3 ON Table1.ID = Table3.ID;

CodePudding user response：

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;

CodePudding user response：

This way you don't lose any ID if an ID is not in one of the tables


SELECT
COALESCE(t3.ID, taux.ID) as ID
COALESCE(t3.Location2, taux.LastLocation) as LastLocation

FROM (SELECT
COALESCE(t2.ID, t1.ID) as ID,
COALESECE(t2.Location2, t1.Location1) as LastLocation
FROM Table1 t1
FULL JOIN Table2 t2 ON t1.ID = t1.ID) taux
FULL JOIN  Table3 t3 ON t3.ID = taux.ID;

CodePudding user response：

SELECT Table1.ID, Table1.Name, case when Table3.Location3 is not null then Table3.Location3 when Table2.Location2 is not null then Table2.Location2 else Table1.Location1 end AS Location1 FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID LEFT JOIN Table3 ON Table1.ID = Table3.ID;