I am struggling with this join, and need help with the syntax.
I want to return all the opportunities in a territory 'abc'.
opportunities are related via lookup to user by ownerId field
in myTerr there is a lookup to user by terrManager. there is also a terr field.
I want something like this:
select opportunity.id, opportunity.ownerId, myTerr.terrManager, myTerr.terr
from opportunity
inner join myterr
on opportunity.ownerId = myTerr.terrManager
Best Answer
Since the object that these two tables have in common is the User object, you should build your query from that level, and include subqueries for the two child objects (Opportunity and myTerr__c). This is further complicated by the fact that the Opportunity.OwnerId is a standard field that doesn't let you automatically do this. So you need two things:
Opportunity_Owner__c
and the relationship name isOwned_Opportunities__r
.Example:
Example:
Now you can loop through the returned Users, and refer to each of the child lists separately.
I notice that this doesn't exactly give you what you say you want, which is all of the Opportunities in a specific Territory. The above code will only do this if each User has exactly 1 territory. Any more, and there doesn't seem to be a way to identify an Opp with a specific territory. You may want to consider a custom lookup on the Opportunity to the myTerr object, which would make this query very simple.