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

2
  • 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, 2019 at 11:45
  • I know but adding fields to an object is not needed if it's solvable via a SQL query.
    – eazy4me
    Mar 19, 2019 at 11:52

1 Answer 1

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]

1
  • Yes that is exactly what i wanted. Your solution did the job. Thanks :)
    – eazy4me
    Mar 19, 2019 at 11:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .