0

I have a requirement to query for master objects that have no corresponding detail objects. This would be similar to querying for Accounts with no Opportunities.

In SQL I might do this:

SELECT Id FROM Account A LEFT JOIN Opportunity O ON O.AccountID = A.Id WHERE O.AccountId IS NULL.

What is the SOQL way of doing this?

3

I would do the following query :

List<Account> accounts = [SELECT Id, Name FROM Account 
                           WHERE Id NOT IN (SELECT AccountId from Opportunity)];

Updated:

List<TransactionHeader__c> transactions = [SELECT Account__c from TransactionHeader__c limit 10000];
List<Account> accounts = [SELECT Id, Name FROM Account 
                           WHERE Id NOT IN (SELECT Account__c from TransactionHeader__c)];

Debug Log :

09:45:06.025 (25652305)|SOQL_EXECUTE_BEGIN|[1]|Aggregations:0|select Account__c from TransactionHeader__c limit 10000
09:45:06.130 (130013480)|SOQL_EXECUTE_END|[1]|Rows:10000
09:45:06.143 (143338699)|SOQL_EXECUTE_BEGIN|[2]|Aggregations:0|select Id, Name from Account where Id NOT IN (select Account__c from TransactionHeader__c)
09:45:06.356 (356276543)|SOQL_EXECUTE_END|[2]|Rows:2774
  • Unfortunately this will crash and burn the minute you hit more than 2000 or so opportunities. You could get a little further by adding group by AccountId in the subquery, but to OP's point, I don't think there is a good LEFT JOIN equivalent in SOQL. The only other option I can think of would be batch Apex, which could scale (in theory) to any number of Opportunities. – Jonathan Hersh Aug 18 '14 at 23:34
  • I disagree with that Jonathan. My child object have more than 100k of records and the query return more than 2000 accounts, and it doesn't crash. I updated my answer. – Cloud Ninja Aug 18 '14 at 23:46
  • Your updated query also includes a limit on your child object. 2000 might be an older limit, but are you sure this will scale beyond 10000 matching child objects? – Jonathan Hersh Aug 18 '14 at 23:52
  • Actually I will correct myself a little bit sorry. The only limit you have is the number of query rows for the same transaction which is 50000 records, but the sub-query doesn't count in this limit – Cloud Ninja Aug 18 '14 at 23:54
  • Yep pretty sure ^^ – Cloud Ninja Aug 18 '14 at 23:59
1

If the relationship between the 2 objects is literally Master-Detail, you can add a rollup summary to the Master that counts the Details, then query for masters where the count is zero.

0

One of the option could be

Map<Id,Account> acctsWithOpps = new Map<Id,Account>([SELECT Id,(SELECT Id FROM Opportunities) FROM Account]);
System.debug(acctsWithOpps);

for (Account account : acctsWithOpps.values())
{
    if(account.Opportunities.size()==0)
    {
        System.debug(account + ' : ' + account.Opportunities);
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.