[SalesForce] How to write an Soql query to fetch all the contact which has opportunites only in stage “closed won”

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.

Best Answer

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.

Related Topic