parent-child and child-parent relationships in one query SOQL

Question

Is it possible to write a query to access parent-child and child-parent objects, in one SOQL query? I have a scenario where, I need to access Account Objects from child and child of Account too, in the same query. Example:

Select Id,
(Select Id,Name, (Select Id, Address from Addresses__r) from x__r.Account),
x__r.Account.Name
From x

Answer 1

Yes, it is possible to do so.

In the example below, we are using sub-query to retrieve Ids of all Account Territories (parent-to-child relationship) and, at the same time, we are using child-to-parent relationship to retrieve TimeZoneSidKey of the User who has created the record.

SELECT Id, (SELECT Id FROM Accounts_Territories__r), CreatedBy.TimeZoneSidKey FROM Account