0

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.

  • Sorry but it is not clear, you should know your table schemes.Where is Source defined? – McNets Oct 16 at 8:39
  • You question is hard to answer, what exactly are you trying to fetch from the 3 tables? – Yannick Liekens Oct 16 at 8:42
  • Source is made up. Sorry I was not clear. Subscriberkey could be in any of the 3 tables and could be in all 3. I want to know if we have the same subscriberkey in multiple tables and if so which. e.g. value 001 is only in Currently_in_welcome so source = E and 002 is in Currently_in_welcome and At_Risk... so there will be a row where Source = E and R. – neo2049 Oct 16 at 8:43
  • e.subscriberkey come from Currently_in_Welcome e, and no variants. – Akina Oct 16 at 8:53
0

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

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.