What is the best way to find records without attachments?

My current query will pull all records meeting the criteria in the WHERE clause, however I need these records filtered down to those without attachments.

SELECT ID,Acct_Nbr__c FROM CC__c WHERE Status__c = 'Active' AND Code__c = 'PUR'

This query is being used to pull a report using the app Report Builder, which means it needs to be accomplished through a single query.


You can use the below query

SELECT ID,Acct_Nbr__c FROM CC__c WHERE Status__c = 'Active' AND Code__c = 'PUR' AND ID NOT IN (SELECT ParentID FROM Attachment) 

But you should be really careful while using this kind of exclusive queries as it will take up much query time and can exceed 120 second limits if you have very large dataset.


I tried the query and i got

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

So you need to do two queries. One on Attachments to get the ParentId and second with exclusive query to get the records which omit the ParentId we received.

Set<Id> setAttachmentParentId = new Set<Id>();
for(Attachment a : [select ParentId from attachment where parent.type = 'CC__c']){
List<CC__c> lstCC = [SELECT ID,Acct_Nbr__c FROM CC__c WHERE Status__c = 'Active' AND Code__c = 'PUR' AND ID NOT IN : setAttachmentParentId];

Hope it helps.

  • Thank you for this. However, I'm receiving an error about the Attachment Object not being available for Semi-Joins. I did try something to similar to this and came up with that same error. – Mowrite Mar 31 '16 at 14:40

You should look at your CC__c SObject on salesforce and see what fields does it have which can describe the status of either having attachments or not. Assuiming this field is called has_attachment__c and is either true or false your query would look something like:

[SELECT ID,Acct_Nbr__c FROM CC__c WHERE Status__c = 'Active' AND Code__c = 'PUR' and has_attachment__c=true];
  • The object does not have a reference to the attachment, unfortunately. – Mowrite Mar 31 '16 at 14:42

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.