For some reason I cannot figure out how to merge elements of two DE's into one for use in journey builder using SQL. For reference, I work with a higher ed org, and we're looking to bring in contact info as well as major interest and application status, which live on a separate object. In total I'm looking to combine the following elements:

Contact Object

  • ID
  • Email
  • Communication Owner (custom field determining import source)

Custom Program Enrollment Object

  • Application Submit Date (custom field)
  • Student Status (custom field)
  • Major of Interest (custom field)
  • Secondary Major of Interest (custom field)
  • Contact_ID__c (Same ID as contact object for matching purposes)

I built a pair of DE's based on synced data sources, and want only the Program Enrollment data that has a corresponding contact in the Contact object (which has already been filtered down). As such, I used a left outer join to do so, with the following code:

, ProspectiveUG_Contact_Has_ID_Email.Id
, Email
, Student_Status__c
, Major_of_Interest__c
, Secondary_Major_of_Interest__c
, Application_Submit_Date__c
, ProspectiveUG_PE_Has_ID.Contact_ID__c
FROM ProspectiveUG_Contact_Has_ID_Email
LEFT JOIN ProspectiveUG_PE_Has_ID 
    ON ProspectiveUG_Contact_Has_ID_Email.Id = ProspectiveUG_PE_Has_ID.Contact_ID__c

However, once the SQL is run and I've checked the DE, only information from the Contact object is surfacing, with none of the Program Enrollment fields coming over.

My two questions are: one - does what I'm doing make sense, or am I completely off on the use of this SQL and two - if this is the correct process, do you see any obvious steps where I'm going wrong?

  • What primary key fields are defined in your target data extension? Aug 18, 2020 at 16:36
  • Is the relationship between the two DEs 1:1 or can there be multiple enrollment matches for a single contact? Aug 18, 2020 at 16:37
  • Relationship between two DE's are not 1:1, so that may be an issue. Primary Key in the DE was Contact Id (the one labeled "Id" not "Contact_ID__c"
    – SS0022
    Aug 18, 2020 at 16:54
  • Updated my answer below. Aug 18, 2020 at 17:18

1 Answer 1


I'd definitely start by adding some aliases to your query and switching to an INNER JOIN if you want to only return Contacts with a matches in the PE DE.

, co.Id
, co.Email
, pe.Student_Status__c
, pe.Major_of_Interest__c
, pe.Secondary_Major_of_Interest__c
, pe.Application_Submit_Date__c
, pe.Contact_ID__c
FROM ProspectiveUG_Contact_Has_ID_Email co
INNER JOIN ProspectiveUG_PE_Has_ID pe ON pe.Contact_ID__c = co.Id

If you recreate your target DE and make all fields nullable and then set your query to overwrite, you should get all rows in common between the two.

Or if you can identify uniqueness for the 1:M key, you can set your query to update, but in that case, you'd need to ensure you're selecting rows that don't violate the primary key (i.e. you may need to de-duplicate).

  • So went in and recreated this (without aliases, I'm basically brand new to SQL) and while it will run, the new DE is still not pulling in any information from the PE extension.
    – SS0022
    Aug 18, 2020 at 17:18
  • Could be that you're JOINing on the wrong ID or there's no overlap -- especially if you're not getting any errors. Aug 18, 2020 at 17:19
  • Did work for you? If so, would you mind checking the check-mark beside my answer? I'd appreciate it. For more information about accepting answers, take a look at this FAQ. Feb 18, 2022 at 14:48

You must log in to answer this question.

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