6

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

2 Answers 2

4

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:

  • A custom lookup field on Opportunity with an Apex trigger that updates the value from the OwnerId field. This new field will have a relationship you can query from the User level. Let's say the field is Opportunity_Owner__c and the relationship name is Owned_Opportunities__r.

Example:

trigger opptrigger (before insert, before update) {
    for (Opportunity o : Trigger.new)
        o.Opportunity_Owner__c = o.OwnerId;
}
  • A query from the User level that includes both child objects together.

Example:

list<User> theseUsers = [select Id,
    (select terr__c from myTerrs__r),
    (select Id from Owned_Opportunities__r)
    from User
    where Id in :someIds];

Now you can loop through the returned Users, and refer to each of the child lists separately.

for (User u : theseUsers) {
    list<myTerr__c> terrs = u.myTerrs__r;
    list<Opportunity> opps = u.Owned_Opportunities__r;
}

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.

1
  • Thanks for your help Jeremy. You're awesome. I didn't realize how much more involved this would be Jan 14, 2013 at 17:31
2

You should be able to do this with a semi-join, e.g.

select id, ... from opportunity where ownerId in (select terrMgr__c from territory__c where name ='West Coast')

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .