[SalesForce] NOT IN based on another query

I wish to find all the tasks where one custom text field which corresponds to an ID of a custom object, but is not a foreign key which points to a valid custom object.

SELECT Id, ME__ThirdPartyId__c, WhatId FROM Task WHERE ME__ThirdPartyId__c NOT IN (SELECT Id FROM ME__MyCustomObject__c)

I get:

MALFORMED_QUERY: The left operand field in the where expression for outer query should be an id field, cannot use: 'ME__ThirdPartyID__c'

Any ideas?

Best Answer

A simpler way would be to form a set or list of strings which correspond to the Ids for of Me_MyCustomObject__c and then use it in the where clause of the query:

for example:

List<Me_MyCustomobject__c> myCustomObjects = [SELECT Id FROM Me_MyCustomObject__c];
Set<String> setIds = new Set<String>();

for (Me_MyCustomobject__c customObject:myCustomObjects) {
    setIds.add(String.valueOf(customObject.Id));
}

List<Task> filteredTasks = [SELECT Id, ME__ThirdPartyId__c, WhatId FROM Task WHERE Me_ThirdParty__c NOT IN :setIds];
Related Topic