fetching roles - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

fetching roles

Author

Topic

sqlbasiclearner
Starting Member

3 Posts

Posted - 2010-12-01 : 12:31:45

Hi all,

I have a table in which there are three columns present user, roles, flag respectively.

I have some roles A,B,C, D, E and some n number of users. I would like to fetch only those users who have roles A,B and only role C along with these two roles. There may a couple of users who have roles A,B,C & D too. Such kind of users should be excluded from the result set.

Could someone please tell me how to do this.

I have tried this query but somehow I get even the users having other roles along with A,B,C roles.

Thank you in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 12:36:27

[code]
SELECT users
FROM YourTable
GROUP BY users
HAVING COUNT(DISTINCT CASE WHEN roles IN ('A','B','C') THEN roles ELSE NULL END) = 3
AND COUNT(CASE WHEN roles IN ('A','B','C') THEN NULL ELSE roles END) = 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlbasiclearner
Starting Member

3 Posts

Posted - 2010-12-01 : 13:02:41

can I have a still easier solution. I dont understand the one mentioned below. Moreover I tried using the same query and it does not fetch the desired results

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 13:04:44

can you show some sample data and then give results you're expecting to get out of them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlbasiclearner
Starting Member

3 Posts

Posted - 2010-12-01 : 13:16:27

Sample data goes something like this

ROLES_TABLE

USER ROLE FLAG
A X Y
A Y Y
A Z Y
B X Y
B Y Y
B Z Y
B Q Y
C X Y
C Y Y
C Z Y
C T Y

Out of these rows I should get the row only for user A since this person has only roles x,y,z. There are other people with the role Z but in addition to Z role they also have other roles like Q and T so they should not be coming in the result set.

Result set

a X Y
a Y Y
a Z Y

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 13:20:22

[code]SELECT t.*
FROM Table t
INNER JOIN (SELECT USER
FROM YourTable
GROUP BY USER
HAVING COUNT(DISTINCT CASE WHEN ROLE IN ('X','Y','Z') THEN ROLE ELSE NULL END) = 3
AND COUNT(CASE WHEN ROLE IN ('X','Y','Z') THEN NULL ELSE ROLE END) = 0)t1
ON t1.USER = t.USER
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 13:27:50

see your scenarios full illustration below


--illustration table containing your sample
SELECT * INTO #UserRoles
FROM
(
SELECT [USER]='A' ,[ROLE]='X', FLAG='Y' UNION ALL
SELECT 'A', 'Y','Y' UNION ALL
SELECT 'A', 'Z','Y' UNION ALL
SELECT 'B', 'X','Y' UNION ALL
SELECT 'B', 'Y','Y' UNION ALL
SELECT 'B', 'Z','Y' UNION ALL
SELECT 'B', 'Q','Y' UNION ALL
SELECT 'C', 'X','Y' UNION ALL
SELECT 'C', 'Y','Y' UNION ALL
SELECT 'C', 'Z','Y' UNION ALL
SELECT 'C', 'T','Y' 
)t


--actual solution
SELECT t.*
FROM #UserRoles t
INNER JOIN (SELECT [USER]
FROM #UserRoles
GROUP BY [USER]
HAVING COUNT(DISTINCT CASE WHEN [ROLE] IN ('X','Y','Z') THEN [ROLE] ELSE NULL END) = 3
AND COUNT(CASE WHEN [ROLE] IN ('X','Y','Z') THEN NULL ELSE [ROLE] END) = 0)t1
ON t1.[USER] = t.[USER]

--clean up table after use
DROP TABLE #UserRoles


output
----------------------------------------
USER	ROLE	FLAG
A	X	Y
A	Y	Y
A	Z	Y

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources