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
Ek0nomikEk0nomik 

SOQL to filter parent records by existence of child

I have the following query:

 

SELECT Id, Name, (Select Id, LastName, FirstName, Email FROM Contacts WHERE Name LIKE '%Foo%')
FROM Account
WHERE Account.Name LIKE '%Bar%'

 

That returns records even where an account does not have any matching contacts.  I was reading the documentation on relationship queries, but I didn't find a way to achieve what I want from those docs.  Does anyone here know how I could do this?  I imagine there's an easy way to do it, but I'm still new to the SOQL world.

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox
select id,name,(select id,lastname,firstname,email from contacts where name like '%foo%') from account where account.name like '%bar%' and id in (select accountid from contact where name like '%foo%')

Alternatively, you could just query from the contact to begin with. Is there any reason why you need your data queried like this...? You could do it in one query instead of three...

All Answers

sfdcfoxsfdcfox
select id,name,(select id,lastname,firstname,email from contacts where name like '%foo%') from account where account.name like '%bar%' and id in (select accountid from contact where name like '%foo%')

Alternatively, you could just query from the contact to begin with. Is there any reason why you need your data queried like this...? You could do it in one query instead of three...

This was selected as the best answer
Ek0nomikEk0nomik

I appreciate the response, thanks.

 

The advantage of querying it like this is that from an object oriented programming perspective the data is already in the format I need.  I have a list of accounts with child contacts.  If I query at the contact level then I'll have to do some work to come up with a distinct list of accounts and then add the children into them.  But, then the disadvantage of my method would be the performance drawback of the subquery.