Finding values from a table that are *not* in a grouping of another table and what group that value is missing from?

Question

I hope I am not missing something very simple here. I have done a Google search(es) and searched through stackoverflow.

Here is the situation: For simplicity's sake let's say I have a table called "PeoplesDocs", in a SQL Server 2008 DB, that holds a bunch of people and all the documents that they own. So one person can have several documents. I also have a table called "RequiredDocs" that simply holds all the documents that a person should have. Here is sort of what it looks like:

PeoplesDocs:

PersonID   DocID  
--------   -----  
1          A  
1          B  
1          C  
1          D  
2          C  
2          D  
3          A  
3          B  
3          C  

RequiredDocs:

DocID     DocName  
-----     ---------  
A         DocumentA  
B         DocumentB  
C         DocumentC  
D         DocumentD 

How do I write a SQL query that returns some variation of:

PersonID   MissingDocs  
--------   -----------  
2          DocumentA  
2          DocumentB  
3          DocumentD  

I have tried, and most of my searching has pointed to, something like:

SELECT DocID
FROM DocsRequired
WHERE NOT EXIST IN (
SELECT DocID FROM PeoplesDocs)

but obviously this will not return anything in this example because everyone has at least one of the documents.

Also, if a person does not have any documents then there will be one record in the PeoplesDocs table with the DocID set to NULL.

Thank you in advance for any help you can provide,

Ben

Answer 1

How about something like this:

Select ...
From RequiredDocs As RD
    Cross Join People As P
Where Not Exists(
                Select 1
                From PeoplesDocs As PD1
                Where PD1.PersonId = P.PersonId
                    And PD1.DocId = RD.DocId
                )

Answer 2

SELECT
    p.PersonID,
    rd.DocName AS MissingDocs
FROM
    dbo.People p, dbo.RequiredDocs rd
WHERE
    rd.DocID NOT IN (SELECT pd.DocID FROM dbo.PeoplesDocs pd
        WHERE pd.PersonID = p.PersonID)