I'd suggest just breaking this into two queries. One to get the list of
Account Ids given some
Opportunity records, and then another one to query for
Contacts based on the
If this query is inside of an
Opportunity trigger, it'd only require one query (you'd have access to
Opportunity.AccountId through one of the trigger context variables).
[SELECT Id, Email FROM Contact WHERE AccountId IN : <list of Account Ids>]
You could also use a parent-child subquery. If you store the result of this query in a
Map<Id, Account>, you would have easy access to the
Map<Id, Account> accountWithContacts = new Map<Id, Account>([SELECT Id, Name, (SELECT Id, Email FROM Contacts) FROM Account WHERE Id IN : <list of Account Ids>]);
List<Contact> oppContacts = accountWithContacts.get(opportunity.AccountId).get('Contacts');
After second thought, the method below wouldn't really help save any queries in any situation. If you're outside of an
Opporunity trigger, you'd likely need a query to get the
Account Ids, and if you were, including the parent-child subquery to
Opportunity would be useless.
If you do need to keep your number of queries down, then it might be worth it to explore refactoring so your main query is on
Account, using that as a junction table to tie
[SELECT Id, Name, (SELECT Id, email FROM Contacts), (SELECT Id FROM Opportunities) FROM Account WHERE Id IN : <list of Account Ids you get from Opportunities>]
This query would count as 1 SOQL query, with 2 'Aggregate' queries. The transaction limit for these 'Aggregate' queries is currently 300, vs 100 for normal SOQL queries. All of the rows queried from
Opportunity will (individually) count against the 50,000 limit.