Get list of users not in a specific role

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)

Get list of users not in a specific role

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-05-30 : 13:43:23

ere is the make-up of the tables:

[dbo].[PortalUser](
[PortalUserID] [bigint] IDENTITY(1,1) NOT NULL,

...

[dbo].[Role](
[RoleID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_Roles_IsActive] DEFAULT ((1))
...

[dbo].[PortalUserRole](
[PortalUserRoleID] [bigint] IDENTITY(1,1) NOT NULL,
[PortalUserID] [bigint] NOT NULL,
[RoleID] [bigint] NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_PortalUserRole_IsActive] DEFAULT ((1))

I'm asking to get a list of portalUsers that do not have a PortalUserRole records for the rolename I'm checking against. And don't ask me why the person who coded this is searching on rolename not ID. But this is how we're doing it for now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 14:01:19

[code]SELECT pu.*
FROM PortalUser pu
LEFT JOIN PortalUserRole pur
ON pur.[PortalUserID]=pu.[PortalUserID]
LEFT JOIN [Role] r
ON r.[RoleID]=pur.[RoleID]
AND r.[Name]=@Role
WHERE pur.[PortalUserID] IS NULL[/code]

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-05-30 : 14:03:54

Thanks...tried it, but that gives me no results. I tested on a role in which I know there are no PortalUserRole records for and so therefore I should get all users back and I didn't

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-05-30 : 14:04:54

Let me clarify again. Picture no PortalUserRole records for lets say 'testrole'. I should get every PortalUser back in that case. Or if there are a total of 5 portaluser records, and only 2 PortalUserRole records for a certain role, then I should only get 2 back

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 14:07:32

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-05-30 : 14:23:38

thanks I believe this may work. Can I ask why you are doing an AND operation on a JOIN? Isn't there a more formal/standard way to do that or is this good practice?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 14:45:25

quote:
Originally posted by dba123

thanks I believe this may work. Can I ask why you are doing an AND operation on a JOIN? Isn't there a more formal/standard way to do that or is this good practice?

I jsut want the record to be be returned only if its Name value is the one we are interested in.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources