How to write an Soql query to fetch all the contact which has opportunites only in stage "closed won"

Question

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.

Answer 1

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.

Answer 2

should be something like this

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