1

I need a query that returns me all contacts that were involved in an opportunity but ignore the ones that were involved in a opportunity recently (10 last days).

I tried using a subquery, like this:

SELECT Id FROM Contact WHERE Id NOT IN (SELECT ContactId FROM OpportunityContactRole WHERE Opportunity.CreatedDate > 2017-09-03) AND RecordType.DeveloperName = 'Customer'

But it returns me every contact, including the ones that weren't involved in an opportunity, and that won't do. I need the ones with a OpportunityContactRole record.

How can refine this result to include only contacts that are related with an opportunity?

2

You should be able to add a Left Inner Join in addition to your Left Anti Join:

SELECT Id FROM Contact
WHERE RecordType.DeveloperName = 'Customer'
AND Id NOT IN (
    SELECT ContactId FROM OpportunityContactRole
    WHERE Opportunity.CreatedDate > 2017-09-03
)
// below clause ensures there is at least one related Opportunity
AND Id IN (
    SELECT ContactId FROM OpportunityContactRole
)
  • I was under the impression that only one subquery was allowed. Thank you for your answer. – Renato Oliveira Sep 13 '17 at 17:44
  • I thought so too, had to double check before I posted. – Adrian Larson Sep 13 '17 at 17:45
1

You can actually write that in to your query:

SELECT Id FROM Contact WHERE Id IN (SELECT ContactId FROM OpportunityContactRole) and Id NOT IN (SELECT ContactId FROM OpportunityContactRole WHERE Opportunity.CreatedDate = last_n_days:10) AND RecordType.DeveloperName = 'Customer'

As long as you're using all AND filters, you can include multiple sub-queries.

  • I was under the impression that only one subquery was allowed. Thank you for your answer. – Renato Oliveira Sep 13 '17 at 17:44

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.