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
jucuzoglujucuzoglu 

How do I filter a SOQL Query (parent/child) to not show recordsets where the child is empty?

I am using a custom object which is a child of the account object.

 

If I run this query:

 

Select a.Name, (Select Partner_Research_Name__c, Partner_Research_URL__c From Partner_Research_Records__r) From Account a

 

I get a list of all accounts some of which contain a record Partner_Research_Records__r that is blank, and others (when the object is present in the parent object) a set of values.

 

I want to add something to the where clause that will filter out any responses where the Partner_Research_Records__r recordset is null, however I can't seem to figure out the proper syntax.

Best Answer chosen by Admin (Salesforce Developers) 
jhurstjhurst

There are a couple of ways to do this.

 

1: You can filter your query to only look at Accounts that have the child records.  Your query would look like:

 

 

Select a.Name, (Select Partner_Research_Name__c, Partner_Research_URL__c From Partner_Research_Records__r) From Account a where a.id in (select account__c from Partner_Research_Records__c where account__c != '')

 

 

This gathers a list of all the account IDs from your partner research records where the Account__c field is not empty.  This will be a list of all the accounts that have childre.  Applied as the filter, only accounts with children will show up.

 

2. You can create a rollup 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 a.Name, (Select Partner_Research_Name__c, Partner_Research_URL__c From Partner_Research_Records__r) From Account a where a.rollup_summary_field__c > 0

 

Hope this helps.

 

Jay

 

All Answers

jhurstjhurst

There are a couple of ways to do this.

 

1: You can filter your query to only look at Accounts that have the child records.  Your query would look like:

 

 

Select a.Name, (Select Partner_Research_Name__c, Partner_Research_URL__c From Partner_Research_Records__r) From Account a where a.id in (select account__c from Partner_Research_Records__c where account__c != '')

 

 

This gathers a list of all the account IDs from your partner research records where the Account__c field is not empty.  This will be a list of all the accounts that have childre.  Applied as the filter, only accounts with children will show up.

 

2. You can create a rollup 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 a.Name, (Select Partner_Research_Name__c, Partner_Research_URL__c From Partner_Research_Records__r) From Account a where a.rollup_summary_field__c > 0

 

Hope this helps.

 

Jay

 

This was selected as the best answer
najkinajki

Thank you! You just solved my problem as well.
AathiAathi
Thanks Jay. This post helped me with the same problem i was facing. Aathi
rajesh kuranarajesh kurana
Really it irritate me a lot, thank you for the solution sir,
Regards,
GHDsports apk (https://ghdsports.fun/)
sheryl brocksheryl brock
This is solved my problem. Thanks for the information.
AppValley App https://appvalley.one/download-appvalley-ios/
İlker Kocatepeİlker Kocatepe
Your answer is spot on, thank you!