0

I have to query all the accounts where more than 1 contact with the field (primary = true). Let say, I have an Account and it has 3 contacts, 2 of contacts having a field(primary = true). So I have to fetch there 2 contacts only with Account. If I have 3 Contacts and 1 contact of them having a field(primary =true) this contact I don't want to get. Please let me know the SOQL query for this.

1 Answer 1

2

The best way to approach this kind of query - locating parent objects by count and attribute of their children - is often an aggregate query. The skeleton would look something like this:

SELECT count(Id), AccountId
FROM Contact
WHERE Primary = true
GROUP BY AccountId
HAVING count(Id) > 1

You can execute this query in the developer console, or assign it to a List<AggregateResult> in Apex and loop over those records to process them.

For more, see Aggregate Functions and Working with SOQL Aggregate Functions.

4
  • Thanks, David for this really It helps. Can we get Id of Contact also ? becoz by this I am not getting the contact Id.
    – SFDC 44
    Jun 19, 2018 at 13:43
  • No, you cannot do that in an aggregate query. You must use aggregated data (count() etc) and grouped fields only, in your SELECT clause.
    – David Reed
    Jun 19, 2018 at 13:44
  • Can't we add any other fields? I want to add AccountId, name , country etc..Is this possible?
    – SFDC 44
    Jun 25, 2018 at 9:45
  • Rahul, the query in my answer includes AccountId. You cannot include non-grouped or -aggregated fields in an aggregate query. You would need to accumulate the Ids in a Set and perform a second Account query to get these details.
    – David Reed
    Jun 25, 2018 at 11:33

You must log in to answer this question.

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