Salesforce | The inner and outer selects should not be on the same object type on recordtype object

Problem: SOQL does not support using the same object name in the Subquery which is in use in outer Select.

For Example: We wanted to get the name of the object values (Record type object) using the SOQL having two conditions using different object names in the where condition as shown in the example below. SOQL semi-joins on non ID fields are not permitted.

SELECT Id, Name , Isactive, sobjecttype FROM recordtype WHERE Name IN (select Name from recordtype where sobjecttype = 'MyObject' and isactive = false) AND Name IN (select Name from recordtype where sobjecttype = 'MyObject2' and isactive = true )

SOQL QUERY Returns the Error as: The inner and outer selects should not be on the same object type on recordtype object.

Solution: To overcome this problem we used Sets or maps according to the requirement. For the above problem we used sets in the apex as:

oppListActive= [Select Id, Name , Isactive, sobjecttype  from recordtype where sobjecttype = 'MyObject2' and isactive = true ];                        
set<String> activeSet = new Set();
for(recordtype r : oppListActive)
   {
    activeSet.add(r.Name);
   }

System.debug('activeSet== ' + activeSet);
oppListInactive=  [Select Id, Name , Isactive, sobjecttype  from recordtype where sobjecttype = 'MyObject' and isactive = false and Name IN :activeSet ];
System.debug('oppListInactive== ' + oppListInactive);

Output: Above program will return all the record type names in the oppListInactive list that are having (sobject type as MyObject with inactive state) and (sobject type as MyObject2 with active state) for the same record type name.

Webner Solutions is a Software Development company focused on developing CRM apps (Salesforce, Zoho), LMS Apps (Moodle/Totara), Websites and Mobile apps. If you need Salesforce customization, App development or any other software development assistance please contact us at salesforce@webners.com