Parent-Child Accounts in Salesforce
Each one of us might have been plagued by the question of "Extracting an Account and all its children, grand-children ,etc.. accounts in a single query" . Did u find any easy answers?
Well first of all you would wonder what should be format of such a query?
Okay! lets says what about something like:-
Select id,name, (Select id, name, parent.name from Parent ) from Account
I am penning this blog after wadding a lot in the search results thrown by google. So you will find many pointers but it will take a while to figure out the answer.
The above query will not work as the name of the Relation between Account and a Parent Account is "ChildAccounts"
So by corollary the following query should work:-
Select id,name, (Select id, name, parent.name from ChildAccounts ) from Account
Well the above query gets us the immediate children but fails to get the grandchildren and so on...
So what should be the query like? Consider the following
select id,name, parent.name from account where parent.name='Parent' or parent.parent.name='Parent'
The above query will give you 2 levels of child records of a Parent Account.
You can define the levels of Hierarchy Allowed in a custom setting and use it to define the parent.name clause dynamically and get the desired results in a single query.