How to obtain email contacts from the nested query on account object

Question

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

Answer 1

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];