Find Roles that have less functionality than...

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 Roles that have less functionality than...

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-09-01 : 17:46:30

I need to construct a query to find Roles that have less functionality than current Role.

The database design is...

AdminRoleFunctions
AdminRoles
AdminFunctions

So, if I have a roleid of 25 and it has ten functions, I need to find all of the other roles that do not have functions that I don't have.

In other words - I need a list of Roles that have equal or less functions than I have. If a role has even one function that I don't have then I don't want it in the list.

I might need a stored proc for this?

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-09-01 : 18:08:59

I can do it using a cursor in a SPROC by cycling through the Roles and checking if any of "MyFunctions" are NULL...


select RoleFunctions.FunctionID as RoleFunctions, MyFunctions.FunctionID as MyFunctions from (
select FunctionID from AdminRoles as AR
INNER JOIN AdminRoleFunctions as ARF ON AR.RoleID = ARF.RoleID
WHERE AR.RoleID = 1 and ClientID in (1,0)) as RoleFunctions
LEFT JOIN
(select FunctionID from AdminRoles as AR
INNER JOIN AdminRoleFunctions as ARF ON AR.RoleID = ARF.RoleID
WHERE AR.RoleID = 29 and ClientID in (0,1)) as MyFunctions
ON RoleFunctions.FunctionID = MyFunctions.FunctionID

But now I'm curious if it can be done in a single query.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-01 : 18:55:09

Does this match your requirements:

<CODE>
declare @AdminRoles table (
RoleID int,
RoleName varchar(20)
)

declare @AdminRoleFunctions table (
RoleID int,
FunctionID int
)

insert into @AdminRoles
select 1, 'Admin' union all
select 2, 'Operations' union all
select 3, 'Sales' union all
select 4, 'Support' union all
select 5, 'SomeGuy'

insert into @AdminRoleFunctions
select 1, 10 union all
select 1, 20 union all
select 1, 30 union all
select 1, 40 union all
select 1, 50 union all

select 2, 10 union all
select 2, 20 union all
select 2, 30 union all
select 2, 40 union all

select 3, 30 union all
select 3, 100 union all

select 4, 20 union all
select 4, 40 union all

select 5, 200

---------------------------------------------------------------------------

declare
@RoleID int

set @RoleID = 1

---------------------------------------------------------------------------

;with CurrentRole(RoleID, FunctionID)
as (
select ar1.RoleID, ar1.FunctionID
from @AdminRoleFunctions ar1
where ar1.RoleID = @RoleID
)
select distinct r.RoleID, r.RoleName
from @AdminRoles r
inner join
@AdminRoleFunctions ar
on ar.RoleID = r.RoleID
where r.RoleID <> @RoleID
and not exists (
select *
from @AdminRoleFunctions ar2
where ar2.RoleID = r.RoleID
and ar2.FunctionID not in (select FunctionID from CurrentRole)
)
order by r.RoleID
</CODE>

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources