Maximum 2 semi join sub-selects are allowed

Question

While I'm querying on account object based on several conditions from different object I'm getting the below error:

Maximum 2 semi join sub-selects are allowed .

The following the query which I'm using

select Id, Name, PersonHomePhone, PersonMobilePhone, Phone, PersonEmail, City__c from Account
where (RecordTypeId ='012280000000f14AAA' or RecordTypeId = '012280000006I5zAAE' or RecordTypeId = '012280000006I61AAE')
and Occupation__c='Office Staff'
and Id in (Select Contact__c From Vehicle_Relationship__c where Vehicle_ID__r.Class__c='C-Class'  AND  New_Used__c='New'  AND  Service_Advisor__r.Name='abcdef'  )
and Id in(Select Contact__c from Lead__c where CAC_Lead_Status__c='New'  )
and Id in(Select toRole__c From Account_Link__c where Sales_Representative__r.Name='abcdef'  )
and Id in(Select Contact_Id__c From Campaign_Member__c where Campaign_ID__r.Name='abcdef'  )

Why am I getting this error?

Answer 1

The error itself says exactly what is wrong, you have more semi join sub-selects than are allowed. Those are the clauses that look like:

Id IN (/*sub-select*/)

You have four of them, so you'll need to split two out into separate queries. You can just populate a Set<Id> with the values from those queries you remove:

Set<Id> contactIds = new Set<Id>();
for (Lead__c record : [/*query on Lead__c*/])
{
    contactIds.add(lead.Contact__c);
}
for (Campaign_Member__c record : [/*query on Campaign_Member__c*/])
{
    contactIds.add(record.Contact_Id__c);
}

Then you can modify your existing query.

...
AND Id IN :contactIds
AND Id IN (SELECT Contact__c FROM Account_Link__c WHERE ...)
AND Id IN (SELECT ToRole__c FROM Vehicle_Relationship__c WHERE ...)

It doesn't matter which sub-select queries you move out of your original query.

It's also worth noting that you should avoid OR clauses at all costs. They are not at all good for query performance. Instead of:

(RecordTypeId = '<id1>' OR RecordTypeId = '<id2>' OR RecordTypeId = '<id3>')

simply use:

RecordTypeId IN ('<id1>', '<id2>', '<id3>')

Not that you should be using hard-coded Ids either...even better would be to filter on RecordType.DeveloperName:

RecordType.DeveloperName IN ('<name1>', '<name2>', '<name3>')

Another advantage to filtering on the name instead is that it makes your code much easier to understand just by reading it.

Answer 2

Taking a reference from documentation, maximum 2 semi joins are allowed

Multiple Semi-Joins or Anti-Joins

You can combine semi-join or anti-join clauses in a query. For example, the following query returns account IDs that have open opportunities if the last name of the contact associated with the account is like the last name “Apple

SELECT Id, Name 
FROM Account
WHERE Id IN
  (
    SELECT AccountId
    FROM Contact
    WHERE LastName LIKE 'apple%'
  )
  AND Id IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE isClosed = false
  )

You can use at most two subqueries in a single semi-join or anti-join query. Multiple semi-joins and anti-join queries are also subject to existing limits on subqueries per query.

Suggested solutions:

1. Create separate queries to retrieve Contact__c from 4 different objects Vehicle_Relationship__c, Lead__c, Account_Link__c and Campaign_Member__c

2. Store those ids in Set<Id> as contactIds

3. Also store recordtype ids in a Set<Id> as recordtypeIds

4. finally execute the query like this:

   SELECT Id, Name, PersonHomePhone, PersonMobilePhone, Phone, PersonEmail, City__c 
   From Contact
   WHERE RecordTypeId IN:recordtypeIds
   AND Id IN:contactIds
   AND Occupation__c='Office Staff'
   

Moreover, as per your code, you are comparing AccountId with the Contact Ids returned from subqueries, so it will not work.

I think it should be from Contact object rather than Account.