+ Start a Discussion

SOQL across multiple objects?

I'm trying to write a SOQL statement that will give me a count of opportunity objects grouped by contact role account. These fields aren't related to each other - the path is:

Opportunity -->
OpportunityContactRole -->
Contact -->

In SQL I'd just join these tables, but I don't know how to do this in SOQL. Any ideas? Thank you!!
kevin Carotherskevin Carothers
Normally I'd say this;

   (SELECT Id,
      (SELECT Id from OpportunityContactRoles) from Opportunities ), (SELECT ID 
     FROM contacts) 
FROM Account

...But you'll likely get an error message like this;

SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.

So... You'll likely have to write your query into two queries and put it into an apex class of some sort (controller, scheduled, batch, etc...)

Before getting that error, I'm getting this one: Didn't understand relationship 'OpportunityContactRoles' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name.

Is OpportunityContactRoles not related to Opportunity? That would make this so much harder!