Salesforce SOQL Query with Self Join + Relationship (ContentDocument/ContentVersion)

Question

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.

Answer 1

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)