Finding values from a table that are *not* in a grouping of another table and what group that value
- by Bkins
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
