Sql-server – know which table the value comes from in inner join

casesql server

I have a simple query where i do 2 inner joins to combine data from 3 tables. within the table i would like to know which table each value comes from. i attempted the following but the syntax is wrong.

select e.subscriberkey, Source
    WHEN FROM Currently_in_Welcome THEN Source = "E"
    WHEN FROM Sell_Engage_Unknown THEN Source = "S"
    WHEN FROM At_Risk_NonEngagement THEN Source = "R"
from Currently_in_Welcome e
inner join Sell_Engage_Unknown s on e.subscriberkey = s.subscriberkey
inner join At_Risk_NonEngagement r on r.subscriberkey = e.subscriberkey

thanks in advance for all your help.

Best Answer


SELECT COALESCE(table1.value, table2.value, ...) AS value,
       CASE WHEN table1.value IS NOT NULL 
            THEN 'table1'
            WHEN table2.value IS NOT NULL 
            THEN 'table2'
            ELSE 'None' 
       END AS source
FROM maintable
LEFT JOIN table1 ON maintable.field=table1.field
LEFT JOIN table2 ON maintable.field=table2.field