How can I retrieve a list of users not in a give grp.

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

SQL Server Development (2000)

How can I retrieve a list of users not in a give grp.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-05 : 07:30:32

Ken writes "table: users (ID, LName, Fname)
table: grps (ID, Descr)
table: gmembers (ID, GID, UID)
GID is a reference to the grps table ID field
UID is a reference to the users table ID field

This query will get me a result set of users that are a member of a given @GID:

SELECT
users.ID, users.LName + ' ' + users.FName AS UserName
FROM
gmembers INNER JOIN
grps ON gmembers.GID = grps.ID INNER JOIN
users ON gmembers.UID = users.ID
WHERE
(grps.ID = @GID)
ORDER BY
users.LName, users.FName

What I can not figure out is a query to get the exact opposite result set, i.e. same fields returned, but for users NOT in the given @GID, to include users that are in NO groups. Simply changing the WHERE clause to (grps.ID <> @GID) gives me a list of all users that members of groups, but NOT in @GID, I need all the others that aren't in a group as well. Additionally, it still returns users that are a member of @GID, so long as they are also a member of another group ( <> @GID). Users may be a member of several grps. The problem seems to be the users that are a member of multiple groups.

Thanks for your assistance,
Ken"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 07:40:19

To return users that belong to no group:

SELECT
u.ID, u.LName + ' ' + u.FName AS UserName
FROM USERS U
LEFT JOIN
GMEMBERS G ON U.ID = G.UID
WHERE G.GID IS NULL

To return users which do not belong to specified group:

SELECT
u.ID, u.LName + ' ' + u.FName AS UserName
FROM USERS U
WHERE NOT EXISTS(SELECT * FROM GMEMBERS G ON U.ID = G.UID
AND G.GID = @GID)

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 07:41:14

select * from users where id not in (select uid from gmembers where gid = @gid)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources