I came across an interesting question on the Developer Boards the other day, one that I'm sure many other developers have come across at some point or another. The question:
How do I perform a SOQL query that compares two fields on an SObject – something like
SELECT id, subject, CreatedById, OwnerId FROM Task WHERE OwnerId != CreatedById
SOQL does not currently support comparing field values in the WHERE clause and so at first blush it may not seem possible to perform the query. But courtesy of our resident Über guru – @superfell – there is a simple workaround. First, create a formula field on the SObject that performs the same comparison function as the desired WHERE clause. In the example above, that would translate to the following String formula field (say 'Owner_Equals_Creator__c'):
IF (OwnerId == CreatedById, "true", "false")
The SOQL query can then simply reference this formula field to perform the equivalent field comparison:
SELECT id, subject, CreatedById, OwnerId FROM Task WHERE Owner_Equals_Creator__c = 'false'
Thanks to Simon, we have a simple workaround to a common SOQL requirement.