How to get data from two different object with same field if there is no relation between objects using SOQL

Question

How to get data from tow different object with same field if there is no relation between two objects using SOQL there is one common field between two objects.

I am trying the below query :

select Id,field1,field2,**commonfield** from **obj1** 
  where Feature_ID__c in (select **commonfield** from **obj2** )

Getting error like semi join sub selects can only query id fields, cannot use.

How to write the SOQL for this?

Answer 1

Primary or foreign key are the only fields supported in a subquery, as the documentation states:

A semi-join is a subquery on another object in an IN clause to restrict the records returned. [...]
The subquery can filter by ID (primary key) or reference (foreign key) fields.

Therefore to fix this issue you should run separate queries. You also need a Set to hold every value of commonfield from the second object, then loop over SELECT commonfield FROM obj2 to populate it. Now you can run the query on obj1 WHERE Feature_ID__c in :commonFieldSet.

Assuming that Feature_ID__c is a String:

Set<String> featureIds = new Set<String>();
for (Obj2 record : [SELECT commonField FROM obj2]) {
    featureIds.add(record.commonField);
}

List<Obj1> something = [SELECT Id,field1,field2,commonfield FROM obj1 WHERE Feature_ID__c in :featureIds];