1

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.

1 Answer 1

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 = 'test@test.com'
      AND Id IN (SELECT ContactId
                 FROM OpportunityContactRole)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.