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