+ Start a Discussion
michaellee.ampfmichaellee.ampf 

Opportunity Contact Roles and SOQL: Searching for Opportunities that have a specific contact

Hey,

 

This feels like a real 101 SOQL question to me, but I couldn't immediately figure out the best answer.

 

I'd like to be able to bring back all of the opportunities related to a specific contact using the contact role.  Obviously Salesforce does this all the time -- but I'm not sure what the best query to get that result would be.

 

I've got the basic query:

 

SELECT Description, Name, IsClosed, (SELECT OpportunityId,IsPrimary,ContactId FROM OpportunityContactRoles) FROM Opportunity

 

but I'm trying to figure out how to build a good WHERE clause so we only get the Opportunities for a specific set of Contacts.

 

I'm missing something obvious here, I'm sure.  Does anyone have any suggestions?

Best Answer chosen by Admin (Salesforce Developers) 
Rahul SharmaRahul Sharma

You can use query like this:

 

Select (Select OpportunityId From OpportunityContactRoles) From Opportunity where id in (Select OpportunityId From OpportunityContactRole where ContactId != '')

 

All Answers

Rahul SharmaRahul Sharma

You can use query like this:

 

Select (Select OpportunityId From OpportunityContactRoles) From Opportunity where id in (Select OpportunityId From OpportunityContactRole where ContactId != '')

 

This was selected as the best answer
michaellee.ampfmichaellee.ampf

Perfect thanks!  I had gone ahead and done two separate SOQL calls, but will now streamline it to the one call.

 

The take away from this example is the difference between OpportunityContactRoles and OpportunityContactRole -- that was what I missed.  OpportunityContactRoles is what you can call "within" the select clause as a part of the Opportunity, where OpportunityContactRole is the more "independent" version.

mmrrmmrr

Can you please help me in getting the viceversa - i.e. getting the contacts for a specific opportunity.