Return matching rows for a given condition

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

Transact-SQL (2012)

Return matching rows for a given condition

Author

Topic

warsid
Starting Member

1 Post

Posted - 2013-12-12 : 03:26:14

Hi All,

I need help in achieving the below.
Assume you have a table holding data like this…

UserRoles Table

UID RID
U1 R1
U1 R2
U1 R3
U2 R1
U2 R2
U3 R1
U3 R2
U3 R3
U4 R1
U4 R4
U4 R2
U4 R3

For an input of a UserID I need to find another UserID which has the exact same Roles (or more) assigned to it.

Eg. In the above data…. If we provide U1 as input, then the matching candidates are U3 and U4 who perform the same roles as U1.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-12 : 11:27:49

There may be a more efficient way to do this but this seems to work:


;with UserRoles (UID, RID) as (
select 'U1', 'R1' union all
select 'U1', 'R2' union all
select 'U1', 'R3' union all
select 'U2', 'R1' union all
select 'U2', 'R2' union all
select 'U3', 'R1' union all
select 'U3', 'R2' union all
select 'U3', 'R3' union all
select 'U4', 'R1' union all
select 'U4', 'R4' union all
select 'U4', 'R2' union all
select 'U4', 'R3')

select a.[uid]
from userRoles a
join userRoles b on b.[uid] != a.[uid] and b.rid = a.rid
where  b.[uid] = 'U1'
group by a.[uid]
having count(*) >= (select count(*) from userRoles where [uid] = 'U1')

OUTPUT:

uid
----
U3
U4

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources