SELECT values from two tables using third table as common column as link

Question

I'm sorry for the post title. I wasn't sure how to word that... I have a query that is working fine:

SELECT
j.SubscriberKey,
j.JobID,
j.EventDate
from _Sent j
where
DATEPART(m, j.EventDate) = DATEPART(m, DATEADD(m, -1, GETDATE()))
AND DATEPART(yyyy, j.EventDate) = DATEPART(yyyy, DATEADD(m, -1, GETDATE()))

But I need to get another value c.Name in table Campaign_Salesforce, but in order to match I need to match values in yet another table Lead_Salesforce.

So Lead_Salesforce has a column Id which is the same as the SubscriberKey in my query above. Campaign_Salesforce has an Id column which matches the column Consumer_newsletter__c in Lead_Salesforce. Hopefully the picture below can help.

Answer 1

You would need a subquery to connect Lead_Salesforce and Campaign_Salesforce,

Try this:

SELECT
j.SubscriberKey,
j.JobID,
j.EventDate,
x.name
from _Sent j


inner join (SELECT 

            l.id
            ,c.Name

             FROM 

             Lead_Salesforce l 

             inner join Campaign_Salesforce c 

             ON l.Consumer_newsletter__c= c.id)  x

ON j.SubscriberKey = x.id 

where
DATEPART(m, j.EventDate) = DATEPART(m, DATEADD(m, -1, GETDATE()))
AND DATEPART(yyyy, j.EventDate) = DATEPART(yyyy, DATEADD(m, -1, GETDATE()))

For optimisation you might want to might want to perform the subquery in a staging table.