[SalesForce] Child object in where clause

I need to do something like this in SOQL. I tried a lot times, but I get different errors.
A is a parent table, B is a child table

Select C1,C2 from A where (Select count() from B where Status__c = 'Completed') = (Select count() from B)

I want to select all the records in A whose all the child records have the status as completed. I tried with B__r, it did not help.

Best Answer

I think trying something like this will work for you.

Select C1, C2 from A 
where 
ID in (Select ParentId from B where Status__c='Completed') 
and 
Id NOT IN (Select ParentId from Opportunity where Status__c <> 'Completed')

The first part i.e 

(Select ParentId from B where Status__c='Completed')

will ensure the ones with Status Completed is included, Next one will ensure the ones for which even one of the records has Status not equal to Completed is excluded.

Related Topic