I am doubting this is possible in SOQL given the requirements and limitations of the language, but wanted to sanity check to make sure I am not overlooking any possible solutions before I throw in the towel.
We are currently building an azure application for which we need to migrate a large subset of our Salesforce users into. I have been tasked with building a query that will return all of the user records that have a certain field filled in, but more importantly need to eliminate all of the users who share an email with any other user in the SF org. I cannot use apex to go through the user records and eliminate the duplicate values.
In SQL, I believe this could be accomplished with using a where clause like this-
WHERE NOT EXISTS (SELECT 1 FROM User U2 WHERE U1.Email = U2.Email AND U1.Id <> U2.ID)
But so far, the closest I have been able to come to accomplishing this in SOQL is the following query-
SELECT Email, FirstName, LastName, Id, IsActive, Username FROM User WHERE Custom_Field_A__c != null AND isActive = true GROUP BY FirstName, LastName, Username, Id, IsActive, Email HAVING COUNT(Id) = 1 Limit 200
However, there are several problems with this query as is. The first is the only way it works is to limit to 200 records, when the number of users that needs to be returned is a much larger number, and there does not seem to be a way to run multiple batches of the query when aggregating fields. It also needs to return the created and modified dates, which seem to not be able to be included in a query that is aggregating fields. Lastly, the way this is group makes it so that all of the values collectively have to be not shared by any other records, but I really just want to filter out users with a duplicate email, not really caring if they have a duplicate first or last name.
Would it be possible to reform this query in some fashion that it will return all of the users in which the Custom_Field_A__c is not null, and the email is not shared between any other users, and all of the fields mentioned can still be returned, without using apex to assist in some fashion?