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
Timothy SmithTimothy Smith 

SOQL retlationship help - Child to Parent to another Child

I am trying to find the number of Accounts with 8 cases created in the last 5 days.  And I need to bring in another field 'Implementation_Status__c' from Milestone1_Project__c.


Account -Parent
Case - Child to Account
Milestone1_Project__c - Child to Account



         Account
         /         \
Case          Milestone1_Proect__c
  
List<AggregateResult> AggregateResultList = [SELECT AccountId, Account.Name name, COUNT(Id) co, Account.Milestone1_Project__c status1
                                    FROM Case
                                    WHERE CreatedDate = LAST_N_DAYS:5
                                    GROUP BY AccountId, Account.Name
                                    HAVING COUNT(Id)  >= 8];

 
Deepali KulshresthaDeepali Kulshrestha
Hi Timothy,

You can use the inner query for this case. The main object for your query will be Account since it is the parent of both of the other objects.

Your query will look like: 
List<Account> account_List = [SELECT Id, Name,(SELECT Id, name, COUNT(Id) co FROM Cases WHERE CreatedDate = LAST_N_DAYS:5 ), (SELECT Name, Id FROM Milestone1_Projects__c ) FROM Account GROUP BY Id, Name];

If the query doesn't work for you, I suggest you to check the plural name of the case and milestone object since we use plural names in inner queries.

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks and Regards,
Deepali Kulshrestha
www.kdeepali.com