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