We collect unsubscribe survey responses and store them in a DE. I want to run a query to combine the data from this DE with the Job and Sent data views to get a complete picture of what email the survey response relates to.
I want to store the results of the query in a DE with the following primary key combination:
I am trying the run the below query in order to achieve this. The query is of the type UPDATE and my destination DE is currently empty:
, u.Preferences__c AS PreferencesID
, u.Date_Created AS DateCreated
, u.Unsubscribe_Reason__c AS UnsubscribeReason
, u.Other_Feedback__c AS OtherFeedback
, s.EventDate AS SendDateTime
, j.AccountID AS MID
[Unsubscribe Survey] u
LEFT JOIN _Sent s
ON u.JobID = s.JobID AND u.BatchID = s.BatchID AND u.ListID = s.ListID
LEFT JOIN _Job j
ON u.JobID = j.JobID
However, I keep getting a primary key violation error. But here is the thing - in my source DE ([Unsubscribe Survey]) I only have 8 records at the moment and I can guarantee they are unique. I have triple checked this in excel to make sure my eyes are not playing tricks. Even just the SubscriberKeys are completely unique.
However again, if I change it to SELECT DISTINCT, it works and I get all 8 records in the destination DE, which I don't understand.
My understanding was that primary key violations are caused when duplicate rows are returned in a query, but that a "duplicate" is considered a combination of all primary key fields in the destination DE? So I would not have thought the using DISTINCT would make any difference in this case?
Can someone please help me understand what's going on here? Thanks