You need to sign in to do that
Don't have an account?
Mhlangano 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...????];
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.
Please try above query