function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Venki123Venki123 

Query Account Record without Child Object

Hi All,

I need help. I have 15 child object in the Account Object. I want to query a Account record which is not having any child object. Is there any easiest way to do it. I have lots of account records. Can you please suggest a fastest way to query the Account record which is not having any child object.

Thanks for your help in advance,
Venkatesh
PratikPratik (Salesforce Developers) 
Hi Venki,

As long as you want to know the Parent without it's child object, you will have to query on all the childs to know that parent is attached to them or not.

One thing as per my knowledge, you can create a custom field on Account object and update it through trigger whenever child object records will be create on the Account records. So if you want Account without child object, you can query on Accounts where that custom field is blank/equals to zero.

Thanks,
Pratik
SarfarajSarfaraj
Hi Venkatesh

Let me show you an example with 10 child objects of account,
List<Account> accounts = [SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId From Contact) AND Id NOT IN (SELECT AccountId FROM Opportunity)];
accounts = [SELECT Id FROM Account WHERE Id IN :accounts AND Id NOT IN (SELECT AccountId FROM Case) AND Id NOT IN (SELECT AccountId FROM Contract)];
accounts = [SELECT Id FROM Account WHERE Id IN :accounts AND Id NOT IN (SELECT AccountId FROM AccountContactRole) AND Id NOT IN (SELECT ParentId FROM AccountFeed)];
accounts = [SELECT Id FROM Account WHERE Id IN :accounts AND Id NOT IN (SELECT AccountId FROM AccountHistory) AND Id NOT IN (SELECT AccountFromId FROM AccountPartner)];
accounts = [SELECT Id FROM Account WHERE Id IN :accounts AND Id NOT IN (SELECT AccountId FROM Asset) AND Id NOT IN (SELECT ParentId FROM Attachment)];
At line 5 you get all account records that have 0 entry in those 10 childs. This SOQL use is 5 statements for 10 childs. You can use this approach for 15 objects and use 8 statements. You may also go for what Pratik suggested. In fact that is more efficient if you have the freedom to implement all those fields and triggers at this stage. To learn more about SOQL joins refer this,

https://developer.salesforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com

-Akram