+ Start a Discussion
EguiEgui 

Get accounts with only one contact

Hello,

 

I'm struggling to write an SOQL query to get all contacts which are alone on their account.

 

In other words get all accounts with only one contact and get those contacts.

 

Help would be very welcome.

 

Thanks !

Best Answer chosen by Admin (Salesforce Developers) 
Chris760Chris760

Consult the HAVING section of your SOQL SOSL Handbook.  I believe you'd have to do this in two parts though since the HAVING clause doesn't allow you to do semi-joins or anti-joins.... but your query would look something like:

 

Set<Id> singleContacts = new Set<Id>();

 

for(Contact c : [select AccountId, COUNT(Id) FROM Contact GROUP BY AccountId HAVING COUNT(Id) = 1]){

  singleContacts.add(c.AccountId);

  }

 

for(Contact c : [select Id, AccountId, (all the other fields you want) FROM Contact WHERE AccountId IN: singleContacts]){

  //Do what you want to do to all the contacts who are the only contact on their respective accounts.

}

All Answers

Chris760Chris760

Consult the HAVING section of your SOQL SOSL Handbook.  I believe you'd have to do this in two parts though since the HAVING clause doesn't allow you to do semi-joins or anti-joins.... but your query would look something like:

 

Set<Id> singleContacts = new Set<Id>();

 

for(Contact c : [select AccountId, COUNT(Id) FROM Contact GROUP BY AccountId HAVING COUNT(Id) = 1]){

  singleContacts.add(c.AccountId);

  }

 

for(Contact c : [select Id, AccountId, (all the other fields you want) FROM Contact WHERE AccountId IN: singleContacts]){

  //Do what you want to do to all the contacts who are the only contact on their respective accounts.

}

This was selected as the best answer
sushant sussushant sus

hi,

 

 AggregateResult[] groupResults = [select AccountId, Count(id) totalContacts FROM CONTACT WHERE AccountId in : accountIds GROUP BY AccountId];
    List<Account> accountListToUpdate = new List<Account>();
    system.debug('AggregateResult When No Record of Contact for Account : --'+groupResults);
    
    for(AggregateResult agrRes : groupResults){
if((Integer) agrRes.get('totalContacts')==1)// this will check contact is only one or not accountIdToTotalContact.put((Id)agrRes.get('AccountId'),(Integer) agrRes.get('totalContacts')); }
Yogesh BiyaniYogesh Biyani
How can I get this list in developer console?