2

I'm trying to write a SOQL query to retrieve some Salesforce Content records and am having a bit of difficulty figuring out my next step. I want to exclude all versions of a document if a custom field of any version of that document has a value (is not null). Here's slimmed down version of what I am trying to do:

Select  Id, Title
From    ContentVersion
Where   ContentDocumentId Not In
        (
           Select ContentDocumentId,
           From   ContentVersion
           Where  Custom_Field__c != null
        )

So I know that you can't write a subquery that targets the same object as its outer query, so obviously what I've specified above won't work. Any suggestions on what will work?

Thank you.

Patrick
  • 971
  • 9
  • 17

1 Answers1

5

Can You try something like this:

Select C.Id from ContentDocument C where 
    ID not in ( Select ContentDocumentId
        From   ContentVersion
        where Custom_Field__c != null)
Łukasz Skrodzki
  • 452
  • 4
  • 8
  • While I think the answer to the broader question is that you cannot achieve a true self-join equivalent in a single SOQL statement, your suggestion here helped me reach an acceptable solution for my purposes. Thanks! – Patrick Jan 20 '12 at 19:18