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 Answers 2

3

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
)
2
  • I was under the impression that only one subquery was allowed. Thank you for your answer. Sep 13, 2017 at 17:44
  • I thought so too, had to double check before I posted.
    – Adrian Larson
    Sep 13, 2017 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.

1
  • I was under the impression that only one subquery was allowed. Thank you for your answer. Sep 13, 2017 at 17:44

You must log in to answer this question.

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