function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

Parent Account to Child Account SOQl Query

I have an interesting ask from the business, where they want to know all the accounts of a specific type that do not have any children.


Based on that, I thought a left anti join would work:

SELECT Name,Id FROM Account

        (SELECT ParentId FROM Account)


But I get an error message:

        (SELECT ParentId FROM Account)
ERROR at Row:3:Column:31
The inner and outer selects should not be on the same object type



Is there a way to run this is a soql query?

Rahul SharmaRahul Sharma

vgorgolI dont think that would work.

In schema builder it does not shows child relationship of Account with itself selectable.

You would need two queries to perform the same. One to retrieve the parent and second for actual result.



I wish this will help you


//htis hold all the account
      List<Acount> ListOfRecords = [select id from Account];
//This hold that account who have no parent
     List<Account> x = ListofRecord(null,ListOfRecords) ;
//Now pass this to recurions       
     Set<Id> newset = new Set<Id>();  
    //this funcition return that record who parent id we are passing    
    public Static List<Account> ListofRecord (String ID , List<Account> sObjs)
        List<Account> lRecord = new  List<Account>();
        for( Account s : sObjs)
            if(s.parentId == Id){
        return lRecord;
//recursion function find the record which are leaf record in hierarchy    
public Static void jsonChild(List<sObject> x , List<SObject> sObjs)
            for(Account t : x)
                  //This hold that record whose parent id we are passing if cd list is empty means account t is leaf node        
                  List<Account> cd =ListofRecord((,sObjs) ;
    /* If answered Mark as solved it might help someone in need */


No need to code for this. Just an SOQL Query.You make make use of ParentId present in Account like this 


SELECT COUNT(Id),ParentId FROM Account GROUP BY ParentId.


This would give you the count of no of children present under each Account.


Guess this would do it . 


Rahul SharmaRahul Sharma

Nice solutions SammyComesHere, almost had forgotten group by clauses. 



Thank you for the idea!  I tried it and got the following error:


ParentId FROM Account GROUP BY ParentId.
ERROR at Row:1:Column:49
Didn't understand relationship 'ParentId' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.


 Parent Account



Please check your Account Fields. ParentId is a standard Field provided by salesforce.


 List<AggregateResult> lAccount= [SELECT COUNT(Id),ParentId FROM Account GROUP BY ParentId];


I have tried this query in my workbench .. Gotta work

Kendall BuchananKendall Buchanan
Don't forget that the following works:

select Id, (select Id from Accounts__r) from Account
Sneha Bhatkande 1Sneha Bhatkande 1
Hello @vgorgol , 
Try this 

select id,(select id from ChildAccounts) from account