Soql query to get all related contacts of an account in an opportunity

Question

i have SOQL query which queries for some records based on a where condition.

select id, name,account.name ... <other fields> from opportunity where eventname__c='Test Event'

i also need to get the related contact details for the account in the opportunity. ie i need to add the email ids of contact who all are part of the account in the opportunity.

For each opportunity, i need to get all the contacts emailids who are associated with the account in opportunity.

I cant really figure out how to approach this. referring the documentation i can get the contact info of a account using the query

SELECT Name,
(
SELECT LastName
FROM Contacts
)
FROM Account

How can i use this along with opportunity?

Thanks

naomi naomi · Accepted Answer · 2012-03-22T15:04:08

The problem is that you are trying to traverse up from opportunity to its parent (account) and then back down to the children (contacts).

I think you will have to do it in two stages, e.g. roughly like:

id[] accountids = new id[]{};
for (opportunity opp : [select accountid from opportunity where eventname__c='Test Event'])
{
      accountids.add (opp.accountid);  
}
account[] acclist = [select name, (select email from contacts) from account where id in :accountIds];

Soql query to get all related contacts of an account in an opportunity

1 Answers