7

I have an object called Child__c, which has two lookups to Account called Account_Lookup_1__c and Account_Lookup_2__c.

I have the following SOQL query, which works as you'd expect it would

select Id
from Account
where Id in (select Account_Lookup_1__c from Child__c) and
      Id in (select Account_Lookup_2__c from Child__c) 

But I cannot do it with an OR, which is what I really want:

select Id
from Account
where Id in (select Account_Lookup_1__c from Child__c) or
      Id in (select Account_Lookup_2__c from Child__c) 

Thanks to this error:

Semi join sub-selects are not allowed with the 'OR' operator

Are there any hacky SOQL workarounds to this, or do I need to look at selecting them separately and manually merging?

  • Obvious workaround: do the select statements before this query, store the Account_Lookup_1__c and Account_Lookup_2__c each in their own Set<Id> and pass that in to the query -- that may work as a workaround, assuming the number of Child__c are small enough to fit in a collection. – Ray Dehler Jun 9 '14 at 16:13
2

You have to do pre-processing to get this one working. You don't need a separate Set for each lookup, though, since you're filtering them all on the same field.

Set<Id> accountIds = new Set<Id>();
for (AggregateResult aggregate : [
    SELECT Account_Lookup_1__c a1, Account_Lookup_2__c a2
    FROM Child__c
    GROUP BY Account_Lookup_1__c, Account_Lookup_2__c
]){
    accountIds.add((Id)aggregate.get('a1'));
    accountIds.add((Id)aggregate.get('a2'));
}
List<Account> records = [SELECT Id FROM Account WHERE Id IN :accountIds];

However, if your goal is simply to find Account records that have Child__c records associated with them, I highly recommend implementing declarative-lookup-rollup-summaries to implement a Child_Count__c field. Then you can just do:

List<Account> records = [SELECT Id FROM Account WHERE Child_Count__c > 0];

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.