+ Start a Discussion
NakataNakata 

SOQL - How to query with multiple sub-query ?

Good day, 

 

I would like to query some account which are filter by multiple criterias, not sure how can i do it 

 

 

SELECT ID FROM Account
WHERE id not in (select ...)
AND   id not in (select ....)
AND   id not in (select ...)
AND   id not in (select .....)

 and i got the error like Maximum 2 semi-join sub-select are allow , anyone can enlighten me is this a limitation of SOQL ?

 

Thank you !

 

ahab1372ahab1372

you could query the IDs first and store the ID is in a list (or set?) and then query against that list.

(Edit: had this all wrong the first time)

 

 

list<ID> exclusion = new list<id>();
for(Account acc:[select ID from Account where Name like 'test%'])
{
	exclusion.add(acc.id);
}

System.Debug(exclusion);

for(Account acc:[select ID from Account where Name like 'Acme%'])
{
	exclusion.add(acc.id);
}

System.Debug(exclusion);

Account[] accs =  [select ID from Account where Id not in :exclusion];

System.Debug(accs);

 

 

 

You might end up with more than one exclusion list, or do multiple queries and add the results all to the same exclusion list because you cannot have more than 2 IN statements per query:

"Because a great deal of processing work is required for semi-join and anti-join queries, salesforce.com imposes the following restrictions to maintain the best possible performance:

  • Basic limits:

No more than two IN or NOT IN statements per WHERE clause."

 

 

NakataNakata

Thanks , but the query suppose run in schema browser/ SOQL explorer instead of coding side 

Benzeen TalhaBenzeen Talha
Many thanks to ahab1372. I was trying to solve a problem for Person Account and Asset , Case and Order. The problem was I only wanted to query Account ID for which all associated Cases and Order have Archive = True (custom field) and only to have Accounts for which all assets have warranty expired. 

I wrote folllowing and ran it in Batchable context. 

List<Id> archiveAccountList = new List<Id>();    
     List<RecordType> rtypes = [Select name, id FROM RecordType WHERE sObjectType = 'Account' AND IsPersonType = True];

    for(Account acc:[Select Acc.ID, Acc.Type from ACCOUNT Acc where recordtypeid IN:rtypes AND 
        Acc.Id in (Select ORD.AccountId From ORDER ORD where ORD.Archive__c = True)
        AND Acc.Id IN (Select CSE.AccountId From CASE CSE where CSE.Archive__c = True) ])
    {
    archiveAccountList.add(ACC.id);
    }    

List<Id> excludeArchiveAccountList = new List<Id>();
    for(Account acc:[Select Acc.ID, Acc.Type from ACCOUNT Acc where acc.ID IN: archiveAccountList AND 
        Acc.Id in (Select Asst.AccountId From ASSET Asst Where Asst.UsageEndDate >: dateCheckTodayPlus1)])
    {
    excludeArchiveAccountList.add(ACC.Id);
    }    

    for(Account acc:[Select Acc.ID, Acc.Type from ACCOUNT Acc where acc.ID IN: archiveAccountList AND 
        Acc.Id in (Select Asst.AccountId From ASSET Asst Where Asst.UsageEndDate <: dateCheckTodayPlus1)])
    {
    archiveAccountList.add(ACC.Id);
    }    
    query = 'Select ACC.ID From ACCOUNT ACC where ACC.ID IN: archiveAccountList AND ACC.ID NOT IN: excludeArchiveAccountList';