How can we Get the records of contacts where phone= account.phone?

Question

I am trying to get the records of Contacts Whose phone equal to Account.phone.

Query:

select Account.name, name from contact where phone=Account.phone

Answer 1

We cannot do a direct field to field comparison in a SOQL.

As a workaround you can add a formula field(say AccountPhoneMatch__c) on Contact object like,

IF(Phone = Account.Phone, 'true', 'false')

Then, query the Contact object using this formula field, like this:

Select Account.name, name from Contact WHERE AccountPhoneMatch__c = true

See here for a similar article on this.

Answer 2

List<Account> accList= [select Id, Phone from Account];
List<string> accPhones = new List<String>();
for(Account acc : accList){
  accPhones.add(acc.phone);   
}
List<contact> contList= [select Account.name, name from contact where phone In :accPhones];

Answer 3

First of all, we need to find all the contacts corresponding to Account and then for each of the contact we need to check whether their phone is same as that of the Account's Phone.

List<Account> accList =  new List<Account>();
Map<Id, Contact> conMap = new Map<Id, Contact>();
acclist = [Select Id, name, Phone, (Select ID, Name, Phone from Contacts) from Account];
for(Account  a : accList){
    List<Contact> conList = a.Contacts;
    for(Contact c: conList){
        if(c.Phone == a.Phone){
            conMap.put(c.AccountId, c);  
            System.debug(conMap);
        }    
    }
}