Combining Data from Two Synchronized Data Extensions into One New DE

Question

I've just created two synchronized data extensions in Contact Builder and then discovered that both data extension have fields the other does not have. The first synced DE is Account_Salesforce and the second is Contact_Salesforce.

The first DE has account level information and the second has contact level information.

I would like to combine the industry info from the account level and merge that info with the contact level info in a new DE.

I'm a beginner with SQL, but here is what I tried in the parent level Query Studio.

Select 
sm.ID
, sm.Sub_Industry__c
, sm.InsideView_Industry__c
, sm.Industry
, s.ID
, s.AccountID
, s.Email
FROM [Account_Salesforce]
JOIN [Contact_Salesforce] s
ON sm.ID = s.ID

Any help will be greatly appreciated.

Answer 1

Try this:

Select 
sm.ID as sm_ID
, s.ID as s_ID
, sm.Sub_Industry__c
, sm.InsideView_Industry__c
, sm.Industry
, s.AccountID
, s.Email
FROM [Account_Salesforce] sm
RIGHT JOIN [Contact_Salesforce] s
ON sm.ID = s.ID

Assuming you want to get all contact records and match industry from the account table, you want to use a right join in your query (since account is the first table and contact is the second table):

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1).

I am not sure if you are joining on the correct ID (not sure what the ID is vs AccountID) but you’ll need to figure this out as we don’t know how your data is structured. If joining on ID does not give you the desired results, try joining on the AccountID instead.

Also, you were missing the table alias for the account table.