I'm working with a SOQL query that is supposed to find all Cases related to Consumer Contract that have attachments.

Cases -----> Attachment, related to Consumer Contract.

I'm able to narrow down cases that are related to the Consumer Contract, however, I'm unable to only pull those cases that are related AND have attachments.

My current query looks like:

Select Id, (Select Id, ParentId 
              From Case.Attachments 
             WHERE (NOT Description LIKE 'Redaction Audit%'))
  FROM Case 
 WHERE Case_Consumer_Contract_CC__c != null 
   AND CreatedDate > 2016-02-01T00:00:00.000Z

The attachment object has two fields ID, and ParentID.

Thank you in advance!

  • 1
    you need to query two times.. first query Attachments then get ParentId and then query on Case
    – Ratan Paul
    Mar 1, 2016 at 15:43
  • I am able to execute your query, what is the exact problem ? Mar 1, 2016 at 15:46
  • @karthikselva The query runs okay, but I want to pull just cases with attachments, rather than all cases related to Consumer Contract.
    – Mowrite
    Mar 1, 2016 at 15:47

2 Answers 2


Try your SOQL from Attachment instead of doing it from Case

SELECT Id,ParentId From Attachment 
WHERE ParentId IN (
WHERE Case_Consumer_Contract_CC__c != null 
AND CreatedDate > 2016-02-01T00:00:00.000Z
AND (NOT Description LIKE 'Redaction Audit%')
AND Parent.Type = 'Case'


  • ParentId field will have Case objects Id


  • Added AND Parent.Type = 'Case' since Attachment is polymorphic table and free selects with loose conditions might have huge impact on your performance
  • 2
    Rats, I had a great explanation...that was too complicated. Didn't realize the only data being selected was the child list itself. Nice and succinct. No need to talk about semi join inner selects or dlrs.
    – Adrian Larson
    Mar 1, 2016 at 16:08
  • thank you :) but still curious on why I am not able to do semi joins on Attachment object, I am sure your explanation will enlighten me Mar 1, 2016 at 16:30
  • 1
    Added it since it was already mostly typed up. No insight into why they're not allowed, other than just not being supported.
    – Adrian Larson
    Mar 1, 2016 at 16:34

Realized the only data you need is from Attachment, so @karthikselva has the more succinct answer. But if you need more complex data from Case and would like to save queries, some of the following might be helpful for you.

Unfortunately, Attachment does not support the following, but normally you could do:


Which yields the error:

Entity 'Attachment' is not supported for semi join inner selects

So, semi join inner select is not an option. At this point you have a couple options:

  • Use declarative-lookup-rollup-summaries (DLRS) to calculate number of Attachment records per Account.

    • You won't have to write a single Apex Class or Apex Trigger to implement this solution.
    • You get the batch code for free with the same Lookup Rollup Summary, meaning you can backfill this data for all Case records easily.
  • Write an aggregate query on Attachment to figure it out on the fly

I highly recommend the first approach, because querying for the aggregates will require you to at least consume one query row from your limit for each Attachment looking up to an Case.

If you implement DLRS, you need to:

  • Create a number field to write to on Case along the lines of Attachment_Count__c.
  • Create a Lookup Rollup Summary with the following values:

    • Parent Object: Case
    • Child Object: Attachment
    • Relationship Field: ParentId
    • Field to Aggregate: Id
    • Aggregate Operation: COUNT
    • Aggregate Result Field: Attachment_Count__c

Then you can just add the following clause to your query: WHERE Attachment_Count__c > 0.

If you feel the need to add a query on Attachment, you can do the following:

Set<Id> caseIdsWithAttachment = new Set<Id>();
for (AggregateResult aggregate : [
    SELECT ParentId FROM Attachment WHERE Parent.Type = 'Case' GROUP BY ParentId
]) caseIdsWithAttachment.add((Id)aggregate.get('ParentId'));
List<Case> withAttachment = [SELECT ... FROM Case WHERE Id IN :caseIdsWithAttachment];
  • 2
    my rough guess is because of its polymorphic nature. I have never used DLRS and its good to know one alternative approach. Thank you :) Mar 1, 2016 at 16:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .