[SalesForce] SOQL – Select only contacts that have related Opportunities

I have the following SOQL:

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) FROM Contact WHERE Email = 'test@test.com'

I want to restrict this to return only those contacts for which the related Opportunities list is not empty. How can I do this?

Thanks.

Best Answer

This is a good place to apply a semi-join pattern, which is the most common query tool used to limit a query by the presence or absence of related objects. It is made only a little bit more complex here by the fact that Opportunity and Contact are related by a sort of junction object, OpportunityContactRole:

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) 
FROM Contact 
WHERE Email = 'test@test.com'
      AND Id IN (SELECT ContactId
                 FROM OpportunityContactRole)
Related Topic