SOQL Child -->Parent ---->Child

Question

I need to frame a SOQL in such a way that, from child i have to get the parent name and then i also need to get some data of another child of the same parent.

Example :

I have 3 objects

Invoice Subscription Billing Account.

Invoice (Child) ------> Billing Account (Parent) Subscription (Child) -------> Billing Account (Parent)

From Invoices , i needs to get the information of Subscription via Billing Account .

Please find the query i framed

select Name,UM_Billing_Location__r.Name,UM_Billing_Location__r.csconta__Account__r.Name,(select Name from UM_Billing_Location__r.csord__Subscription__r) from UM_InvoiceHeader__c

I dont think so this part is correct (select Name from UM_Billing_Location__r.csord__Subscription__r)

Kindly help Thanks

Answer 1

Not entirely clear about your question. This answer assumes you have two child objects of one parent object.

From A Deeper look at SOQL and Relationship Queries on Force.com you need a Left Outer Join and a Left Inner Join, shown below:

id child1Id = ...;
Parent__c p = [
        select Name, (select ... from Child2s__r where ...)
        from Parent__c
        where Id in (select Parent__c from Child1__c where I = :child1Id)
        ];

String parentName = p.Name;
for (Child2__c child2 : p.Child2s__r) {
    ...
}

You can add where, order by and limit as needed to the Child2__c part of the query.