How can I Access fields from Child1-->Parent-->Child2-->grandchild in SOQL? i need to display fields from all of above objects with filter on child1

Question

I'm New to SOQL. I have a field on child1 called x, I want to retrieve all parent records where x='123' and also display a field from the grandchild. Please help me with the SOQL query.

Answer 1

Should be possible, but depends on which fields you want to get. See following example where we start on Order, display its OrderItems childs and filter those where parent Account have specific child Opportunities.

SELECT Id, Account.Name, (SELECT Id FROM OrderItems)
FROM Order
WHERE AccountId IN (SELECT AccountId FROM Opportunity)

In your case you would start with C2, display parent fields from P, child fields from G and filter results where the lookup to parent P is in query for C1.

So like this.

SELECT Id, P.Name, (SELECT Id FROM G)
FROM C2
WHERE PId IN (SELECT PId FROM C1 WHERE field = '123')