9

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?

1
  • 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
    Commented Jun 9, 2014 at 16:13

1 Answer 1

3

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];

You must log in to answer this question.

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