0

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.

1 Answer 1

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.

7
  • Thank you. The code works. I jave just one more question. Both DEs have the field name "Id". I want to display both in the results so I did sm.Id and also s.Id, but I get the following error. Unable to create temporary data extension: A field named Id already exists on object QueryStudioResults at 2023-02-02T1235220000. How can I display both fields so I can make sure the match is correct? Commented Feb 2, 2023 at 12:33
  • I’ve edited my answer to include both IDs
    – zuzannamj
    Commented Feb 2, 2023 at 12:53
  • You were correct about me possibly matching on the wrong ID. The account ID in Contact_Salesforce should match with ID in Account_Salesforce. I tried to edit the code but got another error. Can you edit the code please? Commented Feb 2, 2023 at 13:07
  • 1
    I think I figured it out. I just edit the last line. ON sm.ID = s.AccountId Commented Feb 2, 2023 at 13:13
  • Happy you figured it out
    – zuzannamj
    Commented Feb 2, 2023 at 13:52

You must log in to answer this question.

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