How to Write SOQL for 2 custom objects which have same fields Level_1__c, Level_2__c,Level_3__c

Question

I have a custom object named Case_Type_Data__c with fields Level_1__c, Level_2__c,Level_3__c and I have another custom object named ERT_Case_Type__c with the same fields Level_1__c, Level_2__c,Level_3__c

I batch copied data from ERT_Case_Type__c to Case_Type_Data__c

When I try to write SOQL in workbench to find out which records didn't get copied, for example:

Select Level_1__c,Level_2__c,Level_3__c From Case_Type_Data__c Where Level_3__c NOT IN (Select Level_3__c From ERT_Case_Type__c)

Workbench throws an error:

MALFORMED_QUERY: semi join sub selects can only query id fields, cannot use: 'Level_3__c'

Please note Id of ERT_Case_Type__c and Id of Case_Type_Data__c does not match hence I can't use Id.

I am looking for a way to Join SOQL in order to get records which didn't copy from **ERT_Case_Type__c **to Case_Type_Data__c

Regards, Carolyn

Answer 1

You cannot perform arbitrary joins in SOQL. If Level_3__c is not an Id field, you cannot perform a join across these objects, as the error message states.

If this field is, say, a text field, you'd need to either extract the data from Salesforce and analyze it locally, or write Apex code to perform queries against the two objects separately and draw out the data you need. Given your statement that you initially used Batch Apex, the latter approach is likely infeasible due to data volume.