SQL join three tables, get value from second, if null, then from thrid-CodePudding

I am using SQL Server 2019.

I have three tables. I want to get the value from second table using inner join, but if the value in second table does not exist, then the value should be retrieved from third table.

I came up with the SQL below, but this returns no data.

Select
Table1.ID,
Case
    When Table2.Value2 Is Not Null Then Table2.Value2
    Else Table3.Value3
End as Value
from Table1
Inner Join Table2 On Table1.ID = Table2.ID2
Inner Join Table3 On Table1.ID = Table3.ID3

I googled, but I could not reach the answer. Any help is appreciated, thank you.

CodePudding user response：

We can use the COALESCE() function here:

SELECT t1.ID, COALESCE(t2.Value2, t2.Value3) AS Value
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID2
LEFT JOIN Table3 t3 ON t1.ID = t3.ID3;

A left, rather than inner, join is required in the event that the second table does not have a matching ID. In this case, the query retains all IDs from the first table and gives the chance for a match from the third table.