SOQL to find contacts related to specific opportunities

Question

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?

Answer 1

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
)

Answer 2

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.