开发者

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

开发者 https://www.devze.com 2022-12-26 16:08 出处:网络
I hope I am not missing something very simple here.I have done a Google search(es) and searched through Stack Overflow.

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)
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号