3

I am trying to identify any CASE records that have a email in custom field that matches personemail in account object. in sql I would write this something like this.. what is the corresponding soql query?

select id from case c
where exists
(select 1 from account a
  where a.personemail = c.contact_email__c )

2 Answers 2

4

You will have to do it in multiple parts as the functionality like in sql does not exist

For a all cases:

Case[] matched_cases = New Case[]{};

for(Case c : [Select ID, CustomField, Account.email From Case]){ //best to limit this if possible based on what you are doing

    if(c.CustomField == c.Account.email)
         matched_cases.add(c);

}

Now if you know the email you could do something like this

[Select ID, customField From Case Where AccountID IN (Select ID From Account Where email = :emailstring)]

It all depends on what you are doing and the context of what you are doing

2

Eric's answer certainly works, and is typically the best option, but in some cases it may be beneficial to create a formula field that can be used for this purpose.

To do so you'd create a new checkbox formula field on the case object, with a value of contact_email__c = account.personemail. This formula returns true if they match, false if not. You can then query against this: SELECT Id From Case WHERE new_bool_formula__c = TRUE.

One other tip that's tangentially relevant: if you're comparing fields on the same object (case.field1 = case.field2) you can even get an index applied to the formula field.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .