How to find accounts with no child accounts using soql?

Question

I have requirement where i need to process account which has no child but has parent and accounts which has no parent and child separately.

I tried

select id, name,(select id, name from Accounts) from Account

But it giving me error.

Any workaround/suggestions would greatly appreciated.

Please Help thanks!

Answer 1

1. You can create a custom field on an account called number_of_children.

2. Write a trigger to

   • On Any Update to the account
   • trigger.new[0] = [Select Count() From Account where ParentID = :trigger.new[0].id] //You will need to bulkily this
   • also check to ensure if a parent is remove from a child (After Update)
     • if(trigger.new[0].ParentID == NULL && trigger.old[0].ParentID != null) Account a = [Select ID From Account Where ID = :trigger.old[0].ParentID]; Update a;

All of this will have to be bulkified and then will need to add a delete and after delete

Once this is done, then you can SOQL query on the number_of_children field any time you want

I would also suggest you limit this trigger to only run when a parent ID field is updated to reduce unnecessary execution of the sql statements.

Answer 2

I don't think it is possible just by SOQL. You will have to use apex code to achieve your goal

Answer 3

To find accounts with no child accounts using soql, you can create a roll-up summary field on the account that is a count of the number of child records. You can then apply a filter to your query to be:

select id, name,(select id, name from Contact) from Account where rollup_summary_field0

Also, you can create a field countContacts__c to update with child records and select only that account that has ZERO childs...

 select id, name,(select id, name from Contact) from Account where  id in (select account__c from Contacts where countContacts__c = '')

Thanks.