How to resolve SOQL query error: ''Nesting of semi join sub-selects is not supported"

Question

Here is my SOQL query below in Alteryx application where I have to extract the Id from Contact object which has two level sub-selects. I could only make the SOQL query to work for 1 level of subselect but not more.

Are there any workarounds for this? It would be of great help if someone can edit my code to make it work.

Select Id from Contact c
Where
c.Id in
(SELECT 
contact__c
FROM 
VIN__c  v
where v.Id in
(SELECT 
vin__c
FROM 
Mileage_Reading__c
WHERE 
CreatedDate >= 2020-06-01T00:00:00Z AND  CreatedDate < 2020-07-01T00:00:00Z))

Answer 1

You cannot go a level deep in NESTED Subquery in SOQL. This is documented here

You cannot nest a semi-join or anti-join statement in another semi-join or anti-join statement.

You can use semi-joins and anti-joins in the main WHERE statement, but not in a subquery WHERE statement.

You can split the above queries into multiple. In Salesforce apex language, this is how you do,

List<Mileage_Reading__c>  lstMileageReading = [SELECT vin__c FROM Mileage_Reading__c WHERE CreatedDate >= 2020-06-01T00:00:00Z AND  CreatedDate < 2020-07-01T00:00:00Z];

List<Contact> lstcontact = [Select Id FROM Contact c
  WHERE
  c.Id in
 (SELECT 
  contact__c
  FROM 
  VIN__c  v
  WHERE v.Id IN :lstMileageReading)]:

If you need this from a third party REST Service, you can create an apex class that exposes this as a REST Utility. Then you can invoke the apex rest service as an API.

Alteryx Connector

You can a Select tools and Join tool to acheive this.