1

I know a thing or two about SQL Queries, but in this case i can't figure out what to do. So in this case we have a Contact_Salesforce DE where all contacts are in. We have another DE named Placement__c_Salesforce. The relationship between the two is one to many. The Placement DE is where the orders of the contacts are in. I want to make a SQL query which only adds a contact to a new DE when this contact did his first order and NOT get contacts in who already did a few orders.

This is the code as far as i have it:

select contact.id as Id
, contact.email as Email
, contact.firstname as FirstName

from contact_salesforce contact

left join placement__c_salesforce placement
on placement.candidate__c = contact.id

where ?????

Hope someone can help me.

Cheers

  • You can also create a new field in Contact object of type "rollup summary" where you can COUNT the number of orders. Then use this field in your SQL query. – zeljazouli Mar 19 '19 at 11:45
  • I know but adding fields to an object is not needed if it's solvable via a SQL query. – wfrankhuizen Mar 19 '19 at 11:52
4

You could try using the SQL HAVING clause. Assuming I've understood your objective; this should work as your WHERE clause:

WHERE contact.id in (
       select candidate__c 
       from [placement__c_salesforce] 
       group by candidate__c 
       having count(1)=1
)

In short, this will only return records where contact.id is only found ONCE in [placement__c_salesforce]

|improve this answer|||||
  • Yes that is exactly what i wanted. Your solution did the job. Thanks :) – wfrankhuizen Mar 19 '19 at 11:38

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.