Nested Query in SOQL

Question

Can someone please help me generate SOQL for the below query. Getting this error - Nesting of semi join sub-selects is not supported

SELECT External_ID_vod__c, FirstName, LastName, Middle_vod__c  
FROM Account
where Id IN (select Account_vod__c from EM_Attendee_vod__c WHERE Id IN (SELECT  Incurred_Expense_Attendee_vod__c
FROM Expense_Header_vod__c 
where CALENDAR_YEAR(CreatedDate) > 2020 and Status_vod__c = 'Paid_in_Full_vod'))

Answer 1

Yes, with WHERE clauses you can go "down" the related list only 1 level, looks like you'd need 2 levels.

Couple ideas.

Can you do it in 2 steps? First select Account_vod__c from EM_Attendee_vod__c..., then pass the results to 2nd query.

See if you can eliminate a level by using rollup summary fields - but with this case might be tricky, rollup of all payments in 2020 might be not possible.

See if you can run a report that's close to what you need (even if it'd only grab these Account_vod__c) and you could use "reporting snapshot" - save the intermediate results of the report in a helper custom object. That could make it easier to query.

See if you can run the query by going "up". For example Account_vod__c is a real lookup/master-detail you could try with something like

select Account_vod__r.External_ID_vod__c, Account_vod__r.FirstName, Account_vod__r.LastName, Account_vod__r.Middle_vod__c
from EM_Attendee_vod__c
WHERE Id IN (SELECT  Incurred_Expense_Attendee_vod__c
FROM Expense_Header_vod__c 
where CALENDAR_YEAR(CreatedDate) > 2020 and Status_vod__c = 'Paid_in_Full_vod')

It's not perfect, it'd give you duplicate accounts if they have multiple attendees but it could work good enough. And in a pinch you could always try to deduplicate it with a GROUP BY Account_vod__r.External_ID_vod__c, Account_vod__r.FirstName, Account_vod__r.LastName, Account_vod__r.Middle_vod__c (although GROUP BY doesn't like to have more than 200 results... you could then cheat with LIMIT + OFFSET if you expect to have 2K accounts max)