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>

Answer 1

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);
}