I'm trying to merge two objects using Automation Studio in Marketing Cloud so a customers opportunities/or no opportunities are all in the one table.

I think this spreadsheet i created will make sense. https://docs.google.com/spreadsheets/d/1DqI63WYpD2yc1pkY4ulDCE9uhC1ABcT5Eb9yhFM8j4k/edit?usp=sharing

There are three types of accounts and opps. 1) Accounts with 1 opp 2) Accounts with many opps 3) Accounts with no opps

I need to have all three types of customers appear in the one table with all opps associated.

Hope this makes sense! Thanks


I think a left join will do the trick:

, a.contactID
, o.opp
, o.name
from Account_Salesforce a
left join Opportunity_Salesforce o on o.contactid = a.contactid

You'll need to make sure the primary key on the target data extension defines uniqueness with more columns than just email and contactID.

  • Thanks Adam! what would an example of a further primary key be? The SQL works but only brings in 1 opportunity, not all of them if 1 or more – Matthew Barb Dec 7 '18 at 6:25
  • It shouldn't be returning just one row, if the schema is as you illustrated in your worksheet. I'd probably recommend no PK on the target -- every field nullable and then do use an overwrite as the action on your query. – Adam Spriggs Dec 7 '18 at 17:49

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.