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 Answers 3

4

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
5
  • 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. Aug 18, 2014 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.
    – brovasi
    Aug 18, 2014 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? Aug 18, 2014 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
    – brovasi
    Aug 18, 2014 at 23:54
  • Yep pretty sure ^^
    – brovasi
    Aug 18, 2014 at 23:59
2

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);
    }
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .