SQL SELECT: "Give me all documents where all of the documents procedures are 'work in progress'"

Question

This one really has me stumped. I have a documents table which hold info about the documents, and a procedures table, which is kind of like a revisions table for each document. What I need to do is write a select statement which gives me all of the documents where all of the procedures have the status "work_in_progress". Here's an example

procedures table:

document_id | status
1           | 'wip' 
1           | 'wip'
1           | 'wip'
1           | 'approved'
2           | 'wip'
2           | 'wip'
2           | 'wip'

Here, I would want my query to only return document id 2, because all of its statuses are work_in_progress. I DO NOT want document_id 1 since one of its statuses is 'approved'. I believe this is relational division I want, but I'm not sure where to start. This is MySQL 5.0 FYI.

Answer 1

SELECT document_name FROM documents WHERE 'approved' NOT IN (SELECT status FROM procedures WHERE procedures.document_id = documents.document_id)

Answer 2

I would do this as:

SELECT document_id, document_name, ...
FROM documents AS d
WHERE NOT EXISTS (SELECT document_id
                  FROM procedures
                  WHERE document_id = d.document_id
                      AND status != 'wip');

That is, "show me all documents where there aren't any rows in procedures with a status other than 'wip'".

So it's coming at it a little sideways from the way you described it. Instead of "all the rows are 'wip'", you verify that there aren't any rows that are not 'wip'.

Answer 3

My preferred method would be this, as it uses a count to check the number that are wip is the same as the number in total

SELECT
    document_id
FROM
    documents
GROUP BY
    document_id
HAVING
    COUNT(*) =
(
    SELECT
        COUNT(*)
    FROM
        documents documents2
    WHERE
        documents2.status = 'wip'
     AND
        documents2.document_id = documents.document_id
)

Answer 4

SELECT document_id FROM documents WHERE document_id NOT IN 
(SELECT DISTINCT document_id FROM procedures WHERE status != 'wip')

Answer 5

Here's one more version of the same thing

SELECT document_id, document_name
FROM documents d 
WHERE 'wip' = ALL(SELECT status 
                  FROM procedures p 
                  WHERE p.document_id = d.document_id);

And another one

SELECT document_id, document_name
FROM documents d
     INNER JOIN procedures p ON d.document_id = p.document_id
GROUP BY document_id
HAVING bit_and(p.status='wip')

EDIT: I wondered how do the working solutions compare to each other in terms of performance, so I tested with ~300k records in procedures and ~15k records in documents with minimum number of columns and indexes covering only document_id on modest hardware

1. NOT EXISTS ~1.2s
2. 'wip' = ALL () ~1.2s
3. HAVING COUNT(*) ~2.5s
4. bit_and() ~2.5s

so it seems that bit_and() does not optimize so well as it could. Also a note, adding index on (document_id, status) half the times presented above.