1

I have the following SOQL:

SELECT Id, Name, (SELECT Id, Name FROM Opportunities) FROM Contact WHERE Email = '[email protected]'

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.

Improve this question

1 Answer 1

Reset to default
6

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 = '[email protected]'
      AND Id IN (SELECT ContactId
                 FROM OpportunityContactRole)
Improve this answer
0

You must log in to answer this question.

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