Issue Merging Two DE's with SQL in Marketing Cloud Automation Studio

Question

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:

SELECT 
  Communication_Owner__c
, 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?

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.

SELECT 
  co.Communication_Owner__c
, 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).