Query based on field equal to field in another query

Question

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!

Answer 1

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)

// The first step is to aggregate the data you want to use in your query
// This will typically be a loop over a trigger context variable, or the result
//   of another query
Set<String> aggregatedValues = new Set<String>();
for(Contact cont :[SELECT <fields> FROM Contact WHERE <filters>]){
    aggregatedValues.add(cont.My_Field__c);
}

// The second step is to use the information we gathered to feed the query
//   we really want to run
// This is the part you're interested in
List<Account> accounts = [SELECT <fields> FROM Account WHERE Some_Field__c IN :aggregatedValues];

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.