0

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?

0

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
| improve this answer | |
  • Thank you so much! That worked; grouped by Contact fields, and aggregated order fields. – TamTams20 Jan 28 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.