Sub Query with Aggregate Function in where clause not working in SOQL Salesforce

Question

I have written a very simple SOQL query in Salesforce but it doesn't work.

SELECT Id, UserId, TerritoryId, IsActive 
FROM UserTerritory 
WHERE UserId NOT IN (SELECT UserId 
                    FROM UserTerritory 
                    GROUP BY UserId  
                    HAVING COUNT(UserId) > 1)

The query above is throwing the following exception:

In Developer Console, it shows "Unknown error parsing query"
In Workbench, it shows:

MALFORMED_QUERY: (SELECT UserId FROM UserTerritory GROUP BY UserId HAVING count(UserId) ^ ERROR at Row:1:Column:114 expecting a right parentheses, found 'GROUP'

belford belford · Accepted Answer · 2018-08-29T22:00:11

Such query doesn't make sense anyway.

Let's say that you have simplified the original SOQL as follows:

SELECT Id, UserId, TerritoryId, IsActive 
FROM UserTerritory 
WHERE UserId NOT IN (SELECT UserId FROM UserTerritory)

Salesforce won't let you do so and the exception will be thrown:

The inner and outer selects should not be on the same object type

If more thorough analysis is expected, additional details on the topic are needed (ideally, data examples).

Sub Query with Aggregate Function in where clause not working in SOQL Salesforce

1 Answers