Comparing subqueries in Apex where clause

Question

I am having a hard time with a SOQL query. I want some data from a table using two like conditions on Name. How can I correct my query so that it is a single query rather than two separate queries?

  Select Id, Name , Isactive, sobjecttype from recordtype
   where (select Name from recordtype where 
              sobjecttype = 'Insurance_Product__c' and isactive = true ) 
       = (select Name from recordtype where sobjecttype = 
         'Policy__c' and isactive = false))

Answer 1

First of all, seems like a very strange query, but this should do it:

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

Answer 2

You should just be able to setup your query like:

SELECT Id, Name , Isactive, SobjectType from RecordType
        WHERE (SobjectType = 'Insurance_Product__c' AND IsActive = true) OR
              (SobjectType = 'Policy__c' AND IsActive = false)  

which will return both Active Insurance Product Record Types and InActive Policy Record types.

Note: otherwise you will definitely need to do a separate query as you can not do self inner joins as stated in the docs here:

You cannot query on the same object in a subquery as in the main query.

Answer 3

using the same object in the inner query is not possible in the Apex so I have done it as:

oppListActive= [Select Id, Name , Isactive, sobjecttype  from recordtype where sobjecttype = 'Insurance_Product__c' and isactive = true ];

          set<String> activeSet = new Set<String>();
             for(recordtype r : oppListActive)
                   {
                       activeSet.add(r.Name);
                   }
           System.debug('activeSet== ' + activeSet);
           oppListInactive=  [Select Id, Name , Isactive, sobjecttype  from recordtype where sobjecttype = 'Policy__c' and isactive = false and Name IN :activeSet ];
          System.debug('oppListInactive== ' + oppListInactive);