SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trouble with NOT IN		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sherrireid
Yak Posting Veteran

USA
50 Posts
Posted - 05/11/2012 :  17:41:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 05/11/2012 :  17:57:52  Show Profile  Reply with Quote
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
	)
Go to Top of Page

sherrireid
Yak Posting Veteran

USA
50 Posts
Posted - 05/18/2012 :  19:51:07  Show Profile  Reply with Quote
Thanks much!

SLReid
Forum Newbie
Renton, WA USA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000