function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mhlangano KhumaloMhlangano Khumalo 

How to query only records that have at least one valid child record in Master-Detail relationship.

Bank_Account__c is the Parent object, Debit_Order__c and Ad_hoc_Debit_Orders__c are child objects. I want my query to only select Bank_Account__c records with Debit_Order_A_c__c = true AND with at least 1 record in in both child objects where the field Active__c = true; 
List<Bank_Account__c> result = [SELECT RC_Account_No__c,  Bank_Account__c.Account__r.Name, Bank__c, Bank_code__c, Bank_A_C_Type__c, A_c_Number__c,
                (SELECT Name, Current_DO__c FROM Bank_Account__c.Debit_Orders__r where Active__c = true AND Date_Processed__c < TODAY),
                (SELECT Name, Debit_Order_amount__c FROM Bank_Account__c.Ad_hoc_Debit_Orders__r where Active__c =true)                 
        FROM Bank_Account__c WHERE Debit_Order_A_c__c = true  AND...????];

 
Andrew EchevarriaAndrew Echevarria
List<Debit_Order__c> ActiveDebit = [Select Id from Debit_Order__c where Active__c = true];
List<Ad_hoc_Debit_Orders__c> ActiveAdhocDebit = [Select Id from Ad_hoc_Debit_Orders__c where Active = true];
Set<Id> ActiveOrders = new Set<Id>();
for(Debit_Order__c o : ActiveDebit){
    ActiveOrders.add(o.Id);
}
for(Debit_Order__c o : ActiveAdhocDebit){
    ActiveOrders.add(o.Id);
}

[Select Name from Bank_Account__c where  Debit_Order_a_c__c = true and (Debit_Order__c IN: ActiveOrders or Ad_hoc_Debit_Orders__c IN: ActiveOrders)];

Assuming that the fields on Bank_Account__c match the child object names, if not change them in the Soql query. Also add additional fields to query from Bank_Account__c.
Ishwar ShindeIshwar Shinde
List<Bank_Account__c> result = [SELECT RC_Account_No__c,  Bank_Account__c.Account__r.Name, Bank__c, Bank_code__c, Bank_A_C_Type__c, A_c_Number__c,
                
(SELECT Name, Current_DO__c FROM Bank_Account__c.Debit_Orders__r where Active__c = true AND Date_Processed__c < TODAY),
               
 (SELECT Name, Debit_Order_amount__c FROM Bank_Account__c.Ad_hoc_Debit_Orders__r where Active__c =true)                 
        
FROM Bank_Account__c 

WHERE Debit_Order_A_c__c = true  
             AND Id = (SELECT Bank_Account__c from Debit_Orders__r where Active__c = true AND Date_Processed__c < TODAY) 

            AND Id = (Select Bank_Account__c from Ad_hoc_Debit_Orders__r where Active__c =true)];

Please try above query 
UC InnovationUC Innovation
Another approach would be to create a rollup field for each child object on the parent object to count the total number of child records with Active__c = true.  Then, you can use the rollup field to query for what you want.