I need to pull from a custom object and where account (look up field) where partner (child object) role = x

Original query:

select id, account
from customobject

My thought for the partner:

select id, account
from customobject
where account in (
 select id from account where id in (
   select accountfromid from partner where role = 'x'))

Is there any way of doing this or do I just need to do a separate query from the partner object then query the custom object with account ids in (x)?

  • 2
    Giving us a more detailed/accurate picture (use the actual API names, please) of your object hierarchy will help determine if this is possible in a single query. Do your customObject and partner SObjects both have a lookup relationship to Account?
    – Derek F
    Feb 2 at 22:33

1 Answer 1


With the following assumptions in mind:

  • "customobjet" has a relationship field (master-detail or lookup) to Account
  • "partner" also has a relationship field (master-detail or lookup) to Account

You're not too far off. What you're looking to do here is called a Semi-Join (about the second half of that page).

    FROM CustomObject__c
    // Both the LHS (left-hand side) and RHS (right-hand side) must not traverse 
    //   relationships, and they must be Ids of the same SObject type (i.e. no 
    //   mixing Account and Contact Ids)

    //    v LHS can be a lookup field
    WHERE Account__c IN (SELECT Account__c FROM Partner__c WHERE Role__c = 'x')
    //                          ^ RHS can also be a lookup field

  • thanks derek! your assumptions were correct we had a look up to the account. thanks thought it needed a relationship to be able to pull so must have been why i was trying the account. thanks for the explaination and article! Feb 6 at 21:44

You must log in to answer this question.

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