I want to know which table the value comes from in inner join

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
CASE 
    WHEN FROM Currently_in_Welcome THEN Source = "E"
    WHEN FROM Sell_Engage_Unknown THEN Source = "S"
    WHEN FROM At_Risk_NonEngagement THEN Source = "R"
END
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.

Pattern

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
...

It’s not possible to do with an INNER JOIN, since well.. you’re joining on the same values.

There might be an easier way to get your solution, but this is what I came up with.

-- Create Test tables
CREATE TABLE #Currently_in_Welcome ( subscriberkey INT )
CREATE TABLE #Sell_Engage_Unknown  ( subscriberkey INT )
CREATE TABLE #At_Risk_NonEngagement  ( subscriberkey INT )

-- Fill with data
INSERT INTO #Currently_in_Welcome(subscriberkey)
VALUES (1),(2),(3)

INSERT INTO #Sell_Engage_Unknown(subscriberkey)
VALUES (1),(3),(4),(5),(8)

INSERT INTO #At_Risk_NonEngagement(subscriberkey)
VALUES (1),(6),(7),(8)

-- Create table to hold temp values, could also use CTE 
CREATE TABLE #CombinedSubKeys ( subscriberkey INT, Source Char(1) )

-- Fill in temp table 
INSERT INTO #CombinedSubKeys
SELECT *
FROM (
SELECT subscriberkey,'E' Source
FROM #Currently_in_Welcome
UNION ALL 
SELECT subscriberkey,'S' Source
FROM #Sell_Engage_Unknown
UNION ALL 
SELECT subscriberkey,'R' Source
FROM #At_Risk_NonEngagement
) Combined

-- Query to see where the values were stored, if values are in different tables, you get a result like E, R
 SELECT t.subscriberkey
      , STUFF(( SELECT ', ' + Source
                FROM #CombinedSubKeys 
                WHERE subscriberkey = t.subscriberkey
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,2,'') AS Source
FROM #CombinedSubKeys t
GROUP BY t.subscriberkey

DROP TABLE #Currently_in_Welcome
DROP TABLE #Sell_Engage_Unknown
DROP TABLE #At_Risk_NonEngagement
DROP TABLE #CombinedSubKeys

As a result you’ll get a list of where the keys are in.
1 E, S, R
2 E

When you join two tables you create a new object, which itself looks and behaves like a table. This new object has all the columns from both the source tables, side by side. For example, say the first table has columns A, B and C and the second has columns D, E and F. After the join the new object will have columns A, B, C, D, E and F. The same principle applies as further joins are included in the query.

Of course the DBMS never actually creates a new table on disk with all these columns. It is purely a conceptual thing useful for explaining how the query is processed.

It is up to you which, if any, of these columns (A through F) is returned from the query. In the example given you write select e.subscriberkey... Therefore subscriberkey value returned comes from the table with alias e. That is Currently_in_Welcome.

As it happens, in this query you have used subscriberkey to join the tables. The comparisons are all “equals” (an equi-join) so the value from the other two tables must be the same as the value from Currently_in_Welcome.