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:
SELECT u.SubscriberKey , u.Preferences__c AS PreferencesID , u.JobID , u.BatchID , u.ListID , u.Date_Created AS DateCreated , u.Unsubscribe_Reason__c AS UnsubscribeReason , u.Other_Feedback__c AS OtherFeedback , j.EmailName , s.EventDate AS SendDateTime , j.FromName , j.FromEmail , j.AccountID AS MID FROM [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