+ 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

         /         \
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