soql join query in SalesForce

Question

I'm struggling with a query, I want to select all Branches, and 'join' Companies (of type Account) with again the Contacts in there. Until now no luck, what am i doing wrong?

SELECT 
b.Id, b.Location,
  (SELECT FirstName, LastName, FROM b.Companies.Contacts 
    //i've tried combinations of __r and __c
  WHERE City == 'New York')  
FROM Branche__c b

my WSDL for this part is built up like this:

<complexType name="Branche__c">
  ..
     <element name="Companies__r" nillable="true" minOccurs="0" type="ens:Account"/>
  ..
</complexType>
..
<complexType name="Account">
  ..
  <element name="Contacts" nillable="true" minOccurs="0" type="tns:QueryResult"/>
  ..
</complexType>

Is it throwing any kind of error with your SOQL statement, or is it just returning 0 rows? — JCD
@JCD Error is ERROR at Row:14:Column:24 First SObject of a nested query must be a child of its outer query. — Bokw
I'm fairly certain that this is related to a Salesforce limitation that prohibits subqueries on objects that don't have a direct relationship with the parent object. You may need to write this in two separate queries - the first that retrieves Branche__c records and their related Companies__c records, and then another that retrieves the Contact records related to those Companies. — JCD
@JCD yeah i was afraid that would be the problem, this limitation of SF... thnx! — Bokw
This question appears to be off-topic because it is about salesforce and should be moved to salesforce beta site of SE — Lenin Raj Rajasekaran

Martin Borthiry Martin Borthiry · Accepted Answer · 2012-07-25T17:55:40

Has a @JCD Told you, this is a Salesforce limitation.

You only can list all Contacts related to Accounts at the same time related to Branches in this way:

SELECT Email, Account.Name, 
FROM Contact
WHERE AccountId in (
 SELECT Companies__c
   FROM Branche__c
)

But if you want to make join between those contacts and branches you should try something like this:

list[] res = new list[]{};
for (Branche__c br : [SELECT Id, Location ,Companies__c FROM Branche__c])
{
     List[] ContactList = [select name, (select email from contacts) from account where id = :br.Companies__c]; 
     res.add(ContactList);
}

soql join query in SalesForce

2 Answers