Query all account records of certain type of contacts in salesforce

Question

In salesforce> I have one checkbox on contact object.

I want to get all accounts and it's all contacts if that checkbox on contacts is checked;

Select Name, (Select Name, checkbox_c From contacts where checkbox_c =true) From Account

If we use the above query then it will return the account with no contacts also. But I don't need account record if this don't have any contact with checkbox__c checked.

Answer 1

Yes, salesforce's soql can be a bit tricky, you can first filter your query with a subquery in the where clause.

SELECT name, 
       (SELECT name 
        FROM   contacts 
        WHERE  checkbox__c = true) 
FROM   account 
WHERE  id IN(SELECT accountid 
             FROM   contact 
             WHERE  checkbox__c = true) 

So the first contact query, in the account query select clause will make sure you only return the contacts for that account which are checked. The where subquery will make sure you only get the accounts who have a contact which is checked.