function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MultimanMultiman 

Nested SOQL Query to get Accounts

Hi 

I need a list of all accounts, where a contact person exists that has a given email address.

I tired this

 

var emailAddress = 'user@mydomain.com';

 

[SELECT Id, Name FROM Account WHERE (SELECT Email FROM Account.Contacts) IN :emailAddress ]

which does not work.

 

Any ideas?

Thanks

Alex

Best Answer chosen by Admin (Salesforce Developers) 
MultimanMultiman

Thanks SimonF, works great if I do

 

select id,name from account where id in (select accountId from contact where email = :emailAddress )

since emailAddress is a single string (not a list).
Thanks

All Answers

SuperfellSuperfell

select id,name from account where id in (select accountId from contact where email in :emailAddresses)
sfdcfoxsfdcfox

Well... SOQL does not really work that way, though. That would return a list of all accounts, and you would still need to iterate through all the results to determine which accounts actually had results. In Salesforce, it would be far more efficient to use [SELECT Id,AccountId FROM Contact WHERE Email IN :emailAddresses], then place all the AccountID values into a Set, then query the relevant account data with [SELECT Id,Name,Etc__c FROM Account WHERE Id IN :accountIds]. SOQL subqueries are not the same as SQL subqueries, and are far less powerful (but they do allow you to easily get parent records and their children far easier than normal SQL).
SuperfellSuperfell

Actually SOQL works exactly that way, your suggestion is more work, and no more efficient than the query i posted.
sfdcfoxsfdcfox

Simon... This is why I don't let me on the forums without coffee. My sincerest apologies. I misread the entire conversation, apparently.
MultimanMultiman

Thanks SimonF, works great if I do

 

select id,name from account where id in (select accountId from contact where email = :emailAddress )

since emailAddress is a single string (not a list).
Thanks
This was selected as the best answer
smysoresmysore

Simon, thanks for your answer -- it helped me as well! :-)