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
kerwintangkerwintang 

querying no. of parent records with child WHERE condition/search criteria

Hi guys,

 

Is it possible to query the no. of parent records where certain criteria is specified for the child records?

 

e.g. possible ways to do it in SQL:

select  count(1) from Parent where exists (select 1 from Child where Child.parentId = Parent.parentId and <add'l criteria>)

select  count(distinct parentId) from Child where <add'l criteria>

 

 

As i've tried, at best, i can only do this:

select Id,(select  Id from Child where <add'l criteria>) from Parent;

then loop thru all retrieved records, count each record where Child is null.

or

select parentId from Child where <add'l criteria>

then loop thru all retrieved records and count distinc parentIds.

 

Both solutions above suffer in performance, though, since you have to retrieve all records to get the correct number.

 

 

Any of you have a better solution?

 

Best Regards,

Kerwin

Best Answer chosen by Admin (Salesforce Developers) 
SuperfellSuperfell

You can use semi-joins, e.g, to get the count of accounts that have a contact in CA.

 

select count() from account where id in ( select accountId from contact where MailingState='ca')

All Answers

SuperfellSuperfell

You can use semi-joins, e.g, to get the count of accounts that have a contact in CA.

 

select count() from account where id in ( select accountId from contact where MailingState='ca')

This was selected as the best answer
kerwintangkerwintang

Thanks for the response.

 

I've read around that you have to change the API to point to 15.0 instead of the current 13.0 i am using (https://www.salesforce.com/services/Soap/u/15.0). Do you know where i have to update this in my application? Is it just in the partner.wsdl? I tried to do this but still encountered the "semi join not supported in this API version" error.

 

Best Regards,

Kerwin

SuperfellSuperfell
download the latest partner wsdl and re-import it. make sure you're not overiding the serverUrl from a configuration file somewhere.
kerwintangkerwintang
You're right. it was overriden somewhere in my app. Works now. Thanks for the help!