Version 3

    Perhaps this will help someone.

     

    /* lists the internalDocID (document number) for all documents containing uploaded files,
       excluding those that have been deleted   

     

    JiveDocumentBody does not link directly to jiveDocument; it must go through JiveDocBodyVersion 
    http://docs.jivesoftware.com/schemas/6_0/sbs/tables/jivedocument.html

    The JiveDocumentBody table contains the actual documents in your system.

    Has a uploaded binary:  jiveDocumentBody.fileName is not NULL

     

    The jiveDocVersion table has a state column that indicates the current status of a doc
    deleted
    incomplete
    published
    archived
    */

     

    use jivecore
    GO

     

    SELECT distinct d.internalDocID
      FROM jiveDocument D,
         jiveDocumentBody B,
         jiveDocBodyVersion BV,
         jiveDocVersion DV
      where
          d.internalDocID = BV.internalDocID
          and b.bodyID = bv.bodyID
       and d.internalDocID = dv.internalDocID
          and dv.state = 'published'  /*  an active document  */
      and B.fileName is not NULL  /* has a binary  */
      group by d.internalDocID
      order by d.internalDocID
    GO