1

I am quite new to SOQL and is trying to obtain a list of email address based on the result of the below nested query which returns the account ID.

Here are the two custom objects related to the account object

Account ==Master-Child==> Stores__c ==Master-Child==> Bank_Details__c

I would like to generate a list of emails to the accounts who has stores that uses a certain bank. So far my SOQL can generate a list of account IDs using the below SOQL but would like to know if there is a way to run a single query to obtain the email address for these account IDs as well. Is it possible?

--This gives me a list of account ID matching the bank criteria: SELECT Stores_Account__r.id FROM Bank_Details__c WHERE Bank_Name__c LIKE '%WBC%

I tried using the below to obtain the email address of these account IDs but end up with an error - The inner select field 'Stores_Account__r.id' cannot have more than one level of relationships

SELECT contact.email, contact.account.id FROM contact WHERE contact.account.id IN (SELECT Stores_Account__r.id FROM Bank_Details__c WHERE Bank_Name__c LIKE '%WBC%)

Regards, Chris

  • You will have to use collections (maps and lists) to store / map the results from the first query, then do a second query for the contacts and store them in a map. then process the results and you could pull data from the collections as needed – Eric Oct 16 '15 at 15:13
2

Following up on my comment here is an example on getting a list of contacts related tot he account with the bank name in your filter:

Set<ID> accIDs = New Set<ID>();

for(Bank_Name__c b : [SELECT Stores_Account__c FROM Bank_Details__c WHERE Bank_Name__c LIKE '%WBC%])
    accIDs.add(b.Stores_Account__c);

Contact[] cons = [Select ID, email from Contact Where AccountID IN :accIds];
  • Thank you Eric, that will seems to be the only solution for this scenario. – user16806 Oct 16 '15 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.