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.


I think trying something like this will work for you.

Select C1, C2 from A 
ID in (Select ParentId from B where Status__c='Completed') 
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.


It is not possible to merge aggregate result and query. try this to solve your problem.

for(a temp : (select id,(select id from b-relashipname) from a) {
    //check the count match here
    Integer sizeofb = temp.b-relationship.size();
    Integer index=0;
    for(b : temp.b-relatioshipname) {
       if(b.status=='completed') index++;
    //your business logic if sizeof b and index matches

One option would be to query the child records, storing the Id of the parent in a Set<Id>, then querying the parent.

For example:

Set<Id> parentIdSet = new Set<Id>(); // Create a set to hold the Ids

for (Child_Object__c obj : [SELECT Parent_Field__c FROM Child_Object__c WHERE Status = 'Completed']) {
  parentIdSet.add(obj.Parent_Field__c); // Loop through the child objects, getting those whose status is "Completed"

for (Parent_Object__c obj : [SELECT Name FROM Parent_Object__c WHERE Id IN :parentIdSet]) {
  // Do something... // Query the parent based on Ids stored in the Set<Id>

This should do as you've asked:

I want to select all the records in A whose all the child records have the status as completed.

This will, as a consequence use two SOQL queries so someone may be able to improve that, but otherwise this should be a decent solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.