Help me in this query

• rams.prsk

  Ten Centuries

  Points: 1241

  More actions

  October 2, 2011 at 11:48 pm

  #258163

  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

  ---------

  232

  233

  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

  -----------

  235

  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

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  October 3, 2011 at 1:00 am

  #1389303

  Please keep to one thread per subject

  http://www.sqlservercentral.com/Forums/Topic1184288-391-1.aspx

  ---

  
  Clear Sky SQL
  My Blog[/url]

• Paul Bradshaw

  SSC Eights!

  Points: 949

  More actions

  October 3, 2011 at 1:52 pm

  #1389798

  Is RoleID a bitmask? Or do higher numbered roles always include all lower-numbered roles?

  Because you could just change the SUM to MAX in the latter case. It would return 3 in that case.