+ Start a Discussion
Charisse de BelenCharisse de Belen 

Get list of parent objects from query on child objects

Hello,

I have a custom sObject called AccountDetail that is the child in a lookup relationship with Account. It is also the parent in a lookup relationship with Opportunity. In my situation, I have Master Accounts that have AccountDetail objects, and I have Child Accounts that have Opportunity objects. Here's a diagram to help you visualize these relationships:

Diagram describing relationship between the relevant sObjects
I need to find all the Child Accounts related to each Master Account based on the relationships between their Opportunity and AccountDetail objects (I understand that organizing the data this way does not seem to make sense, but because of reasons outside of my control, this is how I have to do it). 

The query I am trying to use looks like this:
List<Account> accounts = 
   [SELECT Account.Id, Account.Name FROM Opportunity
    WHERE AccountDetail__c IN 
    (SELECT Id FROM AccountDetail__c
    WHERE Account__r.Id= :masterAccountId)].Account;
but this results in an Illegal assignment from Account to List<Account> error.

How do I make this query work to select all the Child Accounts instead of just one? Is there a better way to query the Child Accounts?
Best Answer chosen by Charisse de Belen
karthikeyan perumalkarthikeyan perumal
Hello, 

No need to store in list. 

For parent Object Details use below pice of code : 
 
for(Opportunity  OppAcc: [SELECT Account.Id, Account.Name FROM Opportunity WHERE AccountDetail__c IN (SELECT Id FROM AccountDetail__c WHERE Account__r.Id= :masterAccountId)].Account;)
{   
    for(AccountDetail__c AccDetails:OppAcc.AccountDetail__c)
         System.debug('ParentAccount Name:'+OppAcc.Account.Name+'   ChildAccount Details Name :'+AccDetails.Id);
}

This code will  gives you a master account and Child account Details. 

Hope this will work. 

Thanks
karthik
 

All Answers

karthikeyan perumalkarthikeyan perumal
Hello, 

No need to store in list. 

For parent Object Details use below pice of code : 
 
for(Opportunity  OppAcc: [SELECT Account.Id, Account.Name FROM Opportunity WHERE AccountDetail__c IN (SELECT Id FROM AccountDetail__c WHERE Account__r.Id= :masterAccountId)].Account;)
{   
    for(AccountDetail__c AccDetails:OppAcc.AccountDetail__c)
         System.debug('ParentAccount Name:'+OppAcc.Account.Name+'   ChildAccount Details Name :'+AccDetails.Id);
}

This code will  gives you a master account and Child account Details. 

Hope this will work. 

Thanks
karthik
 
This was selected as the best answer
Charisse de BelenCharisse de Belen
Hi Karthik,

I changed your code a little to fit my needs:
for(Account acc : [SELECT Account.Id, Account.Name FROM Opportunity WHERE AccountDetail__c IN (SELECT Id FROM AccountDetail__c WHERE Account__r.Id = :masterAccountId)].Account) 
{
    System.debug('ChildAccount Name: ' + acc.Name);                       
}
but I get this error: Loop must iterate over a collection type: Account. I think this is similar to my original error, and the query is returning a single Account instead of a collection of Accounts.

Also, because of the way we are processing the Child Accounts, it would be best if they are stored in a list so I can work with them outside of the for loop.
karthikeyan perumalkarthikeyan perumal
Hello, 

Use below updated code and change it fit your needs.

I have removed .Account in that final SoQL part. and added list. 

hope it will work. 
 
List<Opportunity> Opplst=[SELECT Account.Id, Account.Name FROM Opportunity WHERE AccountDetail__c IN (SELECT Id FROM AccountDetail__c WHERE Account__r.Id= :masterAccountId)];

for(Opportunity  OppAcc: Opplst)
{   
    for(AccountDetail__c AccDetails:OppAcc.AccountDetail__c)
         System.debug('ParentAccount Name:'+OppAcc.Account.Name+'   ChildAccount Details Name :'+AccDetails.Id);
}

Thanks
karthik




 
Charisse de BelenCharisse de Belen
After some more thinking, I tried this and it seems to do what I want:
for(Opportunity opp : [SELECT Account.Id, Account.Name FROM Opportunity WHERE AccountDetail__c IN (SELECT Id FROM AccountDetail__c WHERE Account__r.Id = :masterAccountId)]) 
{   
    System.debug('ChildAccount Name: ' + opp.Account.Name);
}
I can even add each opp.Account to a list if I really need to.

Karthik, since your answer helped me get to this point, I have marked your answer Best Answer.