[SalesForce] SOQL to find contacts related to specific opportunities

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?

Best Answer

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
)
Related Topic