I have a custom object that has for name a string that matches the BillingPostalCode field from different Accounts objects.
I'm querying some accounts, and now I want to limit a query of this custom object to only get the ones which names match the BillingPostalCode of any object from the first query.
Is there any way to achieve this with a where clause on the second query? If not, how could I achieve this?
Thanks in advance!
Best Answer
SOQL does not allow us to compare two fields (more specifically, a field cannot appear in the Right-Hand Side of a filter expression).
In some cases (generally when you're trying to compare a field value on a child record against a field value on a related parent record), we can use a formula field to do the field comparison and then use that formula field in the WHERE clause.
In this situation though, you'll want to use the Aggegate-Query-Update pattern (well, the Aggregate and Query parts of it at least)
That
Some_Field__c IN :aggregatedValues
part contains a variable bind (specifically the:aggregatedValues
part). SOQL allows us to use Apex variables in filter expressions. It's a useful tool that helps us work around some of the limitations of SOQL. Beyond this, you'd also use variable binds to do things like being able to include user input in a query.