Sign Up ›

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

How to locate parent records that have zero child records?

I'm trying build a soql query to find all the parent records that don't have any child records in a "Lookup Relationship". Not Master-Datail relation ship.

 

For example, find all Accounts with no Contacts. 

 

I've tried this:

 

list<Account> accs = [SELECT ID FROM Account WHERE ID NOT IN (SELECT Account.Id FROM Contact)];

 This works in ORGs wothout too many Contacts, but isn't efficient, and will crash if there are more than 50K Contacts.

 

Ideas?

 

Thanks,

Marko LamotMarko Lamot

Did you, instead of this approach, consider also "custom roll-up summary/counter" field on parent of lookup relation?

 

We usualy use this approach when we want to know  the number of childs that parent is having...
ryanjuptonryanjupton

That query WILL give you the data you want but you're right that size can be an issue. Have you tried something like this:

 

list<Account> accountsWOContacts = new List<Account>();
for(Account a: [SELECT ID FROM Account WHERE ID NOT IN (SELECT accountId FROM Contact)]){
    accountsWOContacts.add(a);
}
// do something with accountsWOContacts

 Using a SOQL for loop should remove any limitations because it implicitly uses query and queryMore. BTW you had an error in your original query. Notce that the where should select accountId and not account.Id). Your version gives a nested relationship error.

You can also read more here: http://blogs.developerforce.com/developer-relations/2013/05/basic-soql-relationship-queries.html
sandeep@Salesforcesandeep@Salesforce

Apply this Query in Batch Class and mantain number of count in a list but each time you need to check that new record are not in list. or you can go with  OffSet property of salesforce.
JPClark3JPClark3

This question is actually for a couple of custom objects. I just used Account and Contact as an example. I also wrote the example query as an example, just to ask the question. The relationship is already in a managed package, so we cannot change the relationship. The child object can actually have one of two parent object types.

 

 

I think what I need is something like one of these queries (These queries are not allowed, and fail to compile.)

The actual objects I'm using are Lead_Source__c and Interest__c

//Using the relationship name Interests__c

[SELECT ID FROM Lead_Source__c WHERE (SELECT count() FROM Interests__c) = 0]

//OR

[SELECT ID FROM Lead_Source__c WHERE Interests__c = null]

 

 
ryanjuptonryanjupton

The same principles I originally posted work for custom objects as well. The field names will be different should be all.