apex – Write SOQL nested query
I see 3 options here. The one you are trying to achieve.
Set<Id> preDefinedSet = new Set<Id>();
List<Object1> obj1 = [
SELECT field1, field2, field3
FROM Object1
WHERE field1 IN :preDefinedSet
AND Id IN
( SELECT lookupObject1 FROM Object2 WHERE territory == 'UK' )
];
Second query for the Object2 and return values from Object1.
Set<Id> preDefinedSet = new Set<Id>();
List<Object1> obj1 = [
SELECT lookupObject1.field1, lookupObject1.field2, lookupObject1.field3
FROM Object2
WHERE lookupObject1.field1 IN :preDefinedSet
AND territory == 'UK'
];
It is possible that this will return multiple same Object2 thou. So maybe better to group the Object2 by the lookup field, but be aware that this is limited to 2000 lines returned.
Set<Id> preDefinedSet = new Set<Id>();
List<Object1> obj1 = [
SELECT lookupObject1, max(lookupObject1.field1), max(lookupObject1.field2), max(lookupObject1.field3)
FROM Object2
WHERE lookupObject1.field1 IN :preDefinedSet
AND territory == 'UK'
Group By lookupObject1
];
Read more here: Source link