I am writing a logic where i need all the contact which has all the opportunities in closed won stage.

I.e If we have an contact which has 2 opportunity, one is closed won and other is prospect then this contact should not be present in query result.

if a contact has only 2 opportunities and if both are in closed won stage then that contact should appear in result.

  • Do you have any Custom relationship field on Opportunity pointing to Contact? Or standard Account relationship is in a picture? – Madhavi Bhattad Feb 1 '18 at 6:49
  • I have a custom relationship on opportunity – Nagachandra Feb 1 '18 at 6:55

Below query will help you to filter the Contact on the basis of your requirement.

SELECT Id, LastName, (SELECT Id, Name FROM Opportunities__r WHERE StageName = 'Closed Won') FROM Contact Where ID Not IN (SELECT Contact__c FROM Opportunity WHERE StageName != 'Closed Won') 

However, this query will return all the Contacts including Contacts with no Opportunities as well. If you are only looking for Contact having at least a single Opportunity then do check the contact.Opportunities__r.size() before processing further logic.

Note: Ensure you are having the same relationship names.

| improve this answer | |

should be something like this

select Id,(select Id,Name from opportunities where stage != 'Closed Won') from Contact
| improve this answer | |

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.