+ Start a Discussion

soql filter with 2 columns

Maybe I am overlooking the obvious here...


I have 2 objects Obj1 and Obj2  that are related by 2 fields (fld1 and fld2)


I have List<Obj1>  which contains records and I have List<Obj2> which contains records.


I want to create a 3rd list that contains only records from Obj1 where Obj1.fld1 = Obj2.fld1 AND Obj1.fld2 = Obj2.fld2


In basic sql it would be 2 joins between the 2 tables.


Any help would be greatly appreciated.




I may not have been clear here.


What I meant to say was I want to filter Obj1 based on a Select from Obj2 


List<Obj1>O1 = [Select id from Obj1 where ( fld1 in  :obj2  AND fld2 in :obj2)





Unfortunately this kind of thing is not simple in SOQL. One way that I have solved it is by creating formulas on the objects and using those to fake the query. You would end up with code like this (I tested this and it works):



// fld1fld2__c is a formula field (one on each object) that concatenates fld1 and fld2

List<String> fld1fld2list = new List<String>(); // list of strings to be used for later compare
// populate list
for (Obj2__c obj2 : [SELECT fld1fld2__c FROM Obj2__c]) {

// now get objects from Obj1
List<Obj1__c> obj1s = [SELECT Id,Name,fld1__c,fld2__c FROM Obj1__c WHERE fld1fld2__c IN :fld1fld2list ];



Hope this helps!




Hello ,

            prepare List Of id of obj2 and query it in obj1 and whatever list u'll get add it into third one object.