I need to add additional related list 'Associated Accounts' to Campaign and show the 'Number Of Associated Accounts' in Campaign Detail Page. So far it was pretty straight forward to achieve.
I also need to maintain the associated accounts count for complete Campaign Hierarchy. I started writing the code, however with my limited Apex skills, I could not achieve this without a SOQL (Select) stmt in the for loop, it was required to get the details of the parent Campaign.
Is there any other way of achieving it with some join SOQL queries, so I do not need to search for parent campaign in for loop?

trigger Campaign_Accounts_After on Campaign_Accounts__c (after insert, after delete, after undelete) {

//1. Update Campaign Number of Accounts in Campaign and Parent Campaigns
if(trigger.isInsert || trigger.isUpdate){
    //List<Campaign> parentCampaignsUpdate = new List<Campaign>();
    Set<Id> associatedCampaignIds = new Set<Id>();
    for(Campaign_Accounts__c campaignAccounts : Trigger.new){
        associatedCampaignIds.add(campaignAccounts.Campaign__c);
    }

    List<AggregateResult> campaignAccountsCount = [SELECT Campaign__c, COUNT(Account__c) FROM Campaign_Accounts__c WHERE Campaign__c IN : associatedCampaignIds GROUP BY Campaign__c];

    List<Campaign> campaignsUpdate= new List<Campaign>();
    for(AggregateResult ar : campaignAccountsCount){
        Campaign c = new Campaign();
        //System.debug(ar.get('expr0'));
        c.Accounts_in_Campaign__c = (Integer)ar.get('expr0');
        //c.HierarchyNumberOfAccounts__c = c.Accounts_in_Campaign__c; //for this also I need to iterate all the child campaigns
        c.Id = (Id)ar.get('Campaign__c');
        campaignsUpdate.add(c);

    }
    update campaignsUpdate;

    //Maintain the hierarchy, search for parents
    for (Campaign campaign : [SELECT Accounts_in_Campaign__c, HierarchyNumberOfAccounts__c, ParentId FROM Campaign WHERE Id IN :campaignsUpdate])
    {   
        Campaign childCampaign = new Campaign();
        if(campaign.ParentId != null){
            // In this foor loop: I need to write SOQL to get the Parent Campaign Details and check if Parent has further a parent Campaign or not.
            // I am lost here.
        }
    }
}

Cheers,
Dev