Contacts and Orders Join - Primary Key Violation

Question

I'm trying to pull in a list of contacts who've ordered within the last 2 days into a separate DE with the contact ID as the Primary Key. However, I've tried several SQL Join queries in Marketing Cloud, and continue to run into the following error:

Violation of Primary Key Constraint

Here's my query:

SELECT
c.Id,
c.Email,
c.FirstName,
c.LastName,
o.Id as OrderID,
o.Total_Charges__c,
o.Order_Date__c,
o.Order_Store__c,

FROM Order__c_Salesforce as o
  INNER JOIN Contact_Salesforce as c
     on o.Customer_Contact__c = c.Id

WHERE
DATEDIFF(day,o.Order_Date__c, GetDate()) <= 2
AND
c.email is not null

Any thoughts on how I select one of the many orders a potential customer may have to prevent this issue?

Answer 1

You probably have one contact with more than one order in this period, so the inner join is creating duplicated keys (Id) for the orders.

Since you want contacts, you could group by Id and and use an aggregate function:

SELECT
 c.Id,
 max(c.Email) as Email,
 max(c.FirstName) as FirstName,
 max(c.LastName) as LastName,
 max(o.Id as OrderID) as Id,
 max(o.Total_Charges__c) as Total_Charges__c,
 max(o.Order_Date__c) as Order_Date__c,
 max(o.Order_Store__c) as Order_Store__c,

 FROM Order__c_Salesforce as o
 INNER JOIN Contact_Salesforce as c
  on o.Customer_Contact__c = c.Id

 WHERE
  DATEDIFF(day,o.Order_Date__c, GetDate()) <= 2
  AND
  c.email is not null
 group by c.Id