Self Join (how to retrieve multiple records)
-
Folks,
I have a small table with column1 as UserID and column2 as RoleID
I have multiple roles assigned to some Users
Thus the table entries look something like
1 1
1 2
1 3
2 2
2 3
Now, if I want to retrieve UserID for which Role ID matches exactly as 1 & 2 & 3 (meaning it has all 3 RoleIDs) thus fetching me UserID =1, how exactly should I go about using T-SQL?
Further, just to clariy, if I specify my Role ID as 2 & 3 I should get UserID = 2 in return.
Help, much appreciated.
-
- tripple join
or
where with exists-clause for each condition
e.g.
select *
from myself T1
where exists (select * from myself where id = T1.Userid and RoleID=1)
and exists (select * from myself where id = T1.Userid and RoleID=2)
and exists (select * from myself where id = T1.Userid and RoleID=3)
...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Wouldn't this be simpler (assuming each combinaison is unique)?
Select UserID from MyTable where RoleID in (1, 2, 3) Group by UserID having count(*) = 3
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply