Help me in this query - 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

Help me in this query

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-10-03 : 01:49:01

Hi All,

Iam having a usersroles table with 2 columns USerID and RoleID.And when i ran the below query the output shows as below :

select UserID,(roleid) from dbo.Usersroles where UserID = 23

UserID RoleID
---------
23 2
23 3

which means that a single user is having 2 roleID's hence when we use the below query the output will be as follows using SUM:

select UserID , SUM(roleid) as RoleID from dbo.Usersroles where UserID = 23
group by UserID :
UserID RoleID
-----------
23 5

But i want the query to display the RoleID for the user as 3 as 2(roleid) already exists in 3(roleid) for single user

Thanks,
Ram

rams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 01:57:17

what do you mean by 2(roleid) already exists in 3(roleid) for single user?
is this what you're looking at?


select UserID , MAX(roleid) as RoleID from dbo.Usersroles where UserID = 23
group by UserID

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

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-10-03 : 02:02:44

HI Vishakh,
Thanks for your quick reply...The roleid values are bitmask values here..which means comaprision of 1& 3 should show 3 as 1 already exists in 3.

rams

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources