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
loneboatloneboat 

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!

SuperfellSuperfell

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)
sfdcfoxsfdcfox
SELECT 
    Id, Name, UserRole.Name, Profile.Name,
    (SELECT
        Id, CRM_Territory_Type__c
     FROM Territories__r 
     WHERE CRM_Inactive__c=false 
     )
FROM User
WHERE Id IN (SELECT User__c FROM Territory__c WHERE CRM_Inactive=FALSE)
ORDER BY Name

 
jojoforcejojoforce

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: 

SELECT ID,ParentName,
        (SELECT ID,ChildNAME,ParentID FROM Child__r)
FROM Parent

 

 

To:

 

SELECT ID,ChildName, ParentID.ParentName
FROM Child