Trouble with NOT IN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trouble with NOT IN

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2012-05-11 : 17:41:35

I need to find data that is missing in a table. Below are two tables:

DirRoleDefinitions
RoleDefID Name
89 MED - Consult Resident
90 MED - Consult Attending
91 MED - Night Med Intern 1
100 * GROUP: MED

DirContactRoles
PersonID RoleDefID
113390 89
113390 91
113390 100
118901 90
118901 100
123999 100

I need to find the Group MED and its corresponding Roles which start with MED and then figure out which Person is missing a role.
Each Person should be assigned to all roles in the MED group. Based on the example above, the results I would get would be the following:

PersonID RoleDefID
113390 90
118901 89
118901 91
123999 89
123999 90
123999 91
123999 100

I can get the RoleDefIDs in DirRoleDefinitions that are NOT IN DirContactRoles but I get null values for the PersonID. No matter what I do to the code, I can't quite seem to make the PersonIDs show up.

select dirRoleDefinitions.RoleDefID, DirCOntactRoles.PersonID

from DirRoleDefinitions
left outer join DirContactRoles
on DirContactRoles.RoleDefID = DirROleDefinitions.RoleDefiD
WHERE DirRoleDefinitions.RoleDefID NOT IN
(select DirContactRoles.RoleDefID from DirContactRoles)

Can anyone help? Thanks!

SLReid
Forum Newbie
Renton, WA USA

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-11 : 17:57:52

If you have reference tables that lists all the persons, it is a little easier - if not here is one way to query:

SELECT 
	b.PersonId, a.RoleDefId
FROM                                          
	DirRoleDefinitions a
	CROSS JOIN ( SELECT DISTINCT PersonId FROM DirContactRoles ) b
WHERE NOT EXISTS 
	(
		SELECT * FROM DirContactRoles d
		WHERE d.PersonId = b.PersonId AND d.RoleDefId = a.RoleRefId
	)

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2012-05-18 : 19:51:07

Thanks much!

SLReid
Forum Newbie
Renton, WA USA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources