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: