+ Start a Discussion
Mayank Agarwal 5Mayank Agarwal 5 

SOQL to fetch account name who contains contacts

Hello,

I want to write a SOQL to fetch all those account names which have contacts associted with them.

Also to fetch all those account names which have 10 contacts associted with them.


Thanks,
Mayank
Best Answer chosen by Mayank Agarwal 5
David "w00t!" LiuDavid "w00t!" Liu
List<Account> accountsWithContacts = new List<Account>();
List<Account> accountsWithTenContacts = new List<Account>();
List<Account> accs = [SELECT Id, Name, (SELECT Id FROM Contacts) FROM Account];

for (Account a : accs) {
  if (a.Contacts != null && a.Contacts.size() > 0) {
    accountsWithContacts.add(a);
    if (a.Contacts.size() == 10) {
      accountsWithTenContacts.add(a);
    }
  }
}

The above will give you lists of Accounts with Contacts and Accounts with 10 Contacts. You can loop through the list to get just the names of the Accounts if you wish.

All Answers

David "w00t!" LiuDavid "w00t!" Liu
List<Account> accountsWithContacts = new List<Account>();
List<Account> accountsWithTenContacts = new List<Account>();
List<Account> accs = [SELECT Id, Name, (SELECT Id FROM Contacts) FROM Account];

for (Account a : accs) {
  if (a.Contacts != null && a.Contacts.size() > 0) {
    accountsWithContacts.add(a);
    if (a.Contacts.size() == 10) {
      accountsWithTenContacts.add(a);
    }
  }
}

The above will give you lists of Accounts with Contacts and Accounts with 10 Contacts. You can loop through the list to get just the names of the Accounts if you wish.
This was selected as the best answer
Mayank Agarwal 5Mayank Agarwal 5
Hello David,

Thanks for the reply.

But I have one doubt, can this prevent duplicate records also.


Thanks,
Mayank
Ankit AroraAnkit Arora
Yes! Query will retun all the account with the inner list of all the contacts associated with them. Then by David's code you can get the list of account which have atleast 10 contact (according to me it should be 10 or more >=10) in inner list. There will be no duplicates in it. Also for extra check you can loop the final list of account and out it in set to ensure there are no duplicates.
Mayank Agarwal 5Mayank Agarwal 5
Hello Ankit,

Thanks for the clearification.
It help me a lot.

Thanks,
Mayank