+ Start a Discussion
Ram_SF14Ram_SF14 

How to write a SOQL to retrieve an account record with more than 2 contacts only.....Please help

Arvind KumarArvind Kumar
Hi Ram,

Use Below Soql to retrieve an account record with more than 2 contacts.
[ Select id, name, (select id, name from Contacts) from account ]


Thanks,

Arvind Kumar

Ram_SF14Ram_SF14
I dont want all account records with contacts ....... I only want account records which has only 2 contacts
Prateek BhattPrateek Bhatt
Hi Ram,

Use below SOQL to retrive account records which has only 2 contacts
 
select accountid from contact group by accountid having count(id) = 2


Thanks
Arvind KumarArvind Kumar

Hi Ram,

You need to do certain things to make it work for you - 

1. Create one new custom field on Account (Integer type).

2. By using apex, we would rollup number of contacts to this new custom field.

3. Afterwards, you'll have account records with number of contact .

 

Please let us know, in case you wanting this approach, then i will build code for you 

Thanks

 

Arvind KumarArvind Kumar
Hi Ram,

Use below code. It will work for you.
Map<Id,Integer> mp = new Map<Id,Integer>();

for(Account acc : [Select id, name, (select id, name from Contacts) from account])
{
   mp.put(acc.id,acc.Contacts.size());
}


for(Account acc : [Select Id,Name from Account])
{
    if(mp!=null && mp.containsKey(acc.id))
    {
        if(mp.get(acc.id)==2)
            system.debug('aaaa'+ acc.Name);
     }
       
}


This is second approach. You can retrieve account records which have only 2 contacts.


Thanks,
Arvind Kumar

Ram_SF14Ram_SF14
Thanks for your help Aravind But I was asked to write a SOQL to fetch ... I found the answer which is as follows... List agg = [select accountid from contact group by accountid having count(id)>2];