Salesforce SOQL Query Error on custom relationship

Question

I plugged in the query below:

SELECT Id, UserId,User.Name,Account.Name,TeamMemberRole,LastModifiedDate
FROM AccountTeamMember
WHERE TeamMemberRole = 'Customer Success Manager'
AND Account.CSM__r.Name != User.Name

to try and identify any AccountTeamMember CSMs who are not matching the user in the CSM field of the acct. that the team member is associated with, but I get the following error:

MALFORMED_QUERY: 
AND user.Name != Account.CSM__r.Name
                ^
ERROR at Row:4:Column:17
Bind variables only allowed in Apex code

I think something is wrong with the first operand (Account.CSM__r.Name), but I can't tell what. I've tried swapping them as well but that doesn't change anything. For additional reference, the following query worked successfully (to identify CSM Team Members where Account CSM field is blank):

 SELECT Id, userid,user.Name,account.name,TeamMemberRole,LastModifiedDate
 FROM accountteammember
 WHERE teammemberrole = 'Customer Success Manager'
 AND Account.CSM__c = null

Hoping you all might have some insight into this error, and possibly a suggestion for the correct way to perform this sort of query. Thanks so much in advance!

EDIT:

I just tried "AND Account.CSM__c != Userid" to see if reducing the relationships would help (and also "AND Userid != Account.CSM__c"), but same exact error...

Answer 1

SOQL doesn't currently support direct field to field comparison in WHERE clauses. Please find the article for the same.

As per the article the workaround is formula field that will compare fields and return a value (such as true or false) which you can use in a WHERE clause.