0

I'm trying to merge various fields from our synced data sources in SFMC.

For Account_Salesforce, Contact_Salesforce, and Program_Roster__c_Salesforce, the relationship is the account ID, but they are named differently in the three data sources.

For Program_Group__c_Salesforce, the relationship I am using is the individuals contact ID which is "ID" and matching it to Program_Group__c in the Program_Roster__C_Salesforce data source.

Here is what I tried, but I'm not great at writing sql.

Select 
sm.ID as sm_ID
, cs.AccountID as cs_AccountID
, sm.Name
, sm.Sub_Industry__c
, sm.InsideView_Industry__c
, sm.Industry
, sm.Accreditation_Level__c
, sm.AnnualRevenue
, sm.BillingCity
, sm.BillingState
, sm.Congressional_District__c
, sm.Foreign_Owned__c
, sm.Fortune_Global_500__c
, sm.Fortune_Ranking__c
, sm.Legal_Company_Name__c
, sm.Membership_Level__c
, sm.Membership_Status__c
, sm.MSA_Code__c
, sm.MSA_update__c
, sm.Primary_Business__c
, sm.Ultimate_Parent_Company_Name__c
, sm.Type
, cs.AccountID
, cs.Email
, cs.MailingState
, cs.ID
, pr.program_group__c
, pr.Account_Name__C
, pr.Contact_Name__C
, pr.Program_Group_Name__c
, pg.Department__c
, pg.status__c
, pg.ID
FROM [Account_Salesforce] sm 
Inner JOIN [Contact_Salesforce] cs
ON sm.ID = cs.AccountID
Inner JOIN [Program_Roster__c_Salesforce] pr
ON pr.Account_Name__C = cs.AccountID
Inner Join [Program_Roster__c_Salesforce] pg
On pg.ID = pr.Program_Group__c
5
  • What is the problem with the query, are you getting an error?
    – zuzannamj
    Feb 14, 2023 at 11:19
  • Also, think through the DE hierarchy, I doubt you want to inner join all of them. What is the structure of the results you are aiming to get?
    – zuzannamj
    Feb 14, 2023 at 11:27
  • What's the primary key of the target Data Extension? Feb 14, 2023 at 12:46
  • Right now I'm sending the the data to a non-sendable DE. I'm just testing things out. Most likely, it will be one of the 18 digit contact IDs. Feb 14, 2023 at 12:58
  • I'm not receiving an error, but I'm getting 22+ million results. That doesn't sound right. The Account_Salesforce source has 250k records. The contact_salesforce source has 125k records. Also, I'm not sure I got the join commands correct. Feb 14, 2023 at 12:59

1 Answer 1

2

Try this, but it’s up to you to understand your data and to know what results you want to get:

Select 
sm.ID as sm_ID
, cs.AccountID as cs_AccountID
, sm.Name
, sm.Sub_Industry__c
, sm.InsideView_Industry__c
, sm.Industry
, sm.Accreditation_Level__c
, sm.AnnualRevenue
, sm.BillingCity
, sm.BillingState
, sm.Congressional_District__c
, sm.Foreign_Owned__c
, sm.Fortune_Global_500__c
, sm.Fortune_Ranking__c
, sm.Legal_Company_Name__c
, sm.Membership_Level__c
, sm.Membership_Status__c
, sm.MSA_Code__c
, sm.MSA_update__c
, sm.Primary_Business__c
, sm.Ultimate_Parent_Company_Name__c
, sm.Type
, cs.AccountID
, cs.Email
, cs.MailingState
, cs.ID
, pr.program_group__c
, pr.Account_Name__C
, pr.Contact_Name__C
, pr.Program_Group_Name__c
, pg.Department__c
, pg.status__c
, pg.ID
FROM [Contact_Salesforce] cs 
LEFT JOIN [Account_Salesforce] sm
ON sm.ID = cs.AccountID
LEFT JOIN [Program_Roster__c_Salesforce] pr
ON pr.Account_Name__C = cs.AccountID
LEFT Join [Program_Roster__c_Salesforce] pg
On pg.ID = pr.Program_Group__c

In above query, the Contact table is the main table and it’s supplemented with data from other tables. If the logic should be different, please let us know in comments.

Learn more about SQL joins here: https://www.w3schools.com/sql/sql_join.asp

1
  • Thank you. With some adjustments. I was able to get what I needed. I changed the join commands to Inner Join, Right Outer Join, and then Inner Join. Feb 15, 2023 at 17:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .