Parent to child relationships- put a condition on the # of children

Question

How do I pull in the accounts, with the collection of contacts where the # of contacts are greater than 0?

SELECT account.id, account.name, (
                                   SELECT contact.firstName, contact.lastName 
                                   FROM account.contacts WHERE **count > 0** 
                                    ) 
FROM account LIMIT 100

Answer 1

Phil's solution will work great if you just care about returning accounts that have contacts, just bear in mind you'll get multiple rows for accounts with more than one contact.

If you want an arbitrary number, i.e. 2, you'd need to first query the accounts meeting that criteria than the contacts.

// grab ids of accounts having 2 or more contacts
Set<Id> accountIds = new Set<Id>();
for(AggregateResult agg : [
    SELECT COUNT(Id), AccountId FROM Contact
    GROUP BY AccountId HAVING COUNT(ID) >= 2
]) {
    accountIds.add((Id) agg.get('AccountId'));
}
// query the contacts
List<Contact> contacts = [
    SELECT Account.Name, FirstName, LastName 
    FROM Contact WHERE AccountId in :accountIds
];

Answer 2

Another way to do what you want is to query Contact rather than Account, and pull in the fields you want from Account, for example:

Select AccountId, Account.Name, FirstName, LastName From Contact;

You'll only get Accounts where there are Contacts because of the nature of the relationship (i.e. Account being the Parent of Child).

If you wanted to prevent Contacts been selected without an Account, you could add a where clause of AccountId != null.

I don't think you can do it (with just one SOQL) the way you have expressed it.

Answer 3

if you want to get Accounts that have at least 1 Contact (and all such Contacts for that Account)

select id, name, (select firstname, lastname from Contacts) 
   from Account where
       id IN (select accountId from Contacts)

Choose @Ralph solution if you need exactly Accounts with exactly n Contacts.