Finding values from a table that are *not* in a grouping of another table and what group that value is missing from?
I hope I am not missing something very simple here. I have done a Google search(es) and searched through Stack Overflow.
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开发者_StackOverflow社区 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.
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
)
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)
精彩评论