1

I am not getting the right results back from my join query. I want all Employee_Benefit_Summary__c records that have a Grand_Total_Premium__c value greater than 0 that do not have any related Employee_Session_Benefit__c records.

SELECT Id, Name 
FROM Employee_Benefit_Summary__c 
WHERE Id NOT IN (
    SELECT Employee_Benefit_Summary__c 
    FROM Employee_Session_Benefit__c 
    WHERE Account_Benefit__r.Account__c = '00180000015p27UAAQ' 
    AND Account_Benefit__r.Name LIKE 'Cigna%'
) 
AND Grand_Total_Premium__c != 0

Parent Object: Employee_Benefit_Summary__c Child Object: Employee_Session_Benefit__c

The child objects are all related to the parent object.

Do I maybe have the object queries flipped around. As in should the employee_Session_Benefit__c query be first with the other object as the internal query.

  • FYI, I am currently getting back Summaries that do have Benefits and the summary total is greater than 0. – Matt M Jan 20 '17 at 21:24
  • Don't use hard-coded ids...If you want to filter no child records, remove the WHERE clause. – Adrian Larson Jan 20 '17 at 21:27
  • So, like this? SELECT Id, Name FROM Employee_Benefit_Summary__c WHERE Id NOT IN (SELECT Employee_Benefit_Summary__c FROM Employee_Session_Benefit__c) AND Grand_Total_Premium__c != 0 AND Account_Benefit__r.Name LIKE 'Cigna%' – Matt M Jan 20 '17 at 21:34
2

If you want to write the filter so that you ignore all children, remove the entire WHERE clause on your inner query. You should also use the greater than (>) operator rather than the inequality operator (!=).

SELECT Id, Name 
FROM Employee_Benefit_Summary__c 
WHERE Grand_Total_Premium__c > 0 AND Id NOT IN (
    SELECT Employee_Benefit_Summary__c 
    FROM Employee_Session_Benefit__c
)
  • you rock! I had to add a few more filters but that worked. My final statement was this: SELECT Id, Name FROM Employee_Benefit_Summary__c WHERE Grand_Total_Premium__c > 0 AND Account_Benefit__r.Name LIKE 'Cigna%' AND Account_Benefit__r.Account__r.Name LIKE 'ABC Account' AND Id NOT IN ( SELECT Employee_Benefit_Summary__c FROM Employee_Session_Benefit__c ) – Matt M Jan 20 '17 at 21:50
  • Glad I could help! Note that for answers you find useful, you can vote them up using the arrows next to them. There is of course no obligation to do so, just making sure you're familiar with the mechanics of this site. Cheers! – Adrian Larson Jan 20 '17 at 21:57
1

I've never had much luck trying to get SOQL to return results where a given record has 0 related records under it.

It's probably easier (and perhaps faster) to do the filtering outside of your query in this case.

for(Employee_Benefit_Summary__c ebs :[SELECT Id, Name, (SELECT Id FROM Employee_Session_Benefits__r LIMIT 1) FROM Employee_Benefit_Summary__c WHERE Grand_Total_Premium__c > 0]){
    if(ebs.Employee_Session_Benefits__r.isEmpty()){
        someList.add(ebs);
    }
}

This example takes advantage of the fact that when you include a left outer join (a.k.a. parent-child subquery in the SELECT clause), Salesforce gives you a List of the child sObject embedded in each resulting row of the parent sObject.

If there are no children for a given 'parent', you simply get an empty list, and it's dead simple to check for an empty list.

For those who aren't aware, you access the list of child records by using the relationship name (which I'm guessing is Employee_Session_Benefits__r in this case).

  • Make sure to add a LIMIT 1 clause to your inner query if you go this route. :) – Adrian Larson Jan 20 '17 at 21:38
  • @AdrianLarson yeah, guess there isn't much point in wasting resources if we only need to check if empty. Fixed my derp. – Derek F Jan 20 '17 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.