Find users that are missing from a table....

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 2005 Forums

Transact-SQL (2005)

Find users that are missing from a table....

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-06-23 : 15:58:21

so this is what i have:

XUsersInRoles (t1) - has userID and roleID
XUsers (t5) - has userID and user_name

user_to_group_tbl(t2) - has usergroup_id and user_id
users(t3) - has user_id and user_name

i need to get all users from Xusers that are in the same role, but are not in the table users....

SELECT t5.username FROM XUsersInRoles t1, XUsers t5 where t1.roleid=32 and t5.userid = t1.userid AND not exists (select t3.user_name from user_to_group_tbl t2, users t3 where t2.usergroup_id= 32 and t2.user_id = t3.user_id)

it comes out 0 :(

:((((((((((((((((((((((((

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-06-23 : 16:08:41

i think it's not finding it because they are not both ordered by username, but when i try it like this:

SELECT t5.username FROM xUsersInRoles t1, xUsers t5 where t1.roleid='6F09F8FB-3B9F-466A-B55F-2144C4D1B9F1' and t5.userid = t1.userid AND not exists (select t3.user_name from user_to_group_tbl t2, users t3 where t2.usergroup_id = 32 and t2.user_id = t3.user_id order by t3.user_name ) ordery by t5.username

i get:
Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Sachin.Nand

2937 Posts

Posted - 2010-06-24 : 00:14:51

Try this


SELECT t5.username FROM xUsersInRoles t1, xUsers t5 where t1.roleid='6F09F8FB-3B9F-466A-B55F-2144C4D1B9F1' and t5.userid = t1.userid AND not exists (select top 1 with ties t3.user_name from user_to_group_tbl t2, users t3 where t2.usergroup_id = 32 and t2.user_id = t3.user_id order by t3.user_name ) ordery by t5.username

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources