You need to sign in to do that
Don't have an account?
loneboat
SOQL - Only return objects where subquery has results?
Here's my query:
SELECT Id , Name , UserRole.Name , Profile.Name ,( SELECT Id ,CRM_Territory_Type__c FROM Territories__r WHERE CRM_Inactive__c=false ) FROM User WHERE Territories__r.null ORDER BY Name
Is there any way to get this query to ONLY return results where the subquery has results?
Thanks!
You can use a semijoin to filter the parent rows, something like.
select id, name ... (select id ... from ...) from User where id in (select parentId from territories__c where crm_inactive__c = false)
Since you are more interested in seeing child records, I would drive the SOQL from the Child-To-Parent. I don't know the exact structure of your two objects, so I'll use a very simple example that is similar to your query. The approach below will guarantee that you only have Child records to query and traverse from Child to Parent.
http://www.salesforce.com//us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm#kanchor429
Change From:
To: