Pulling back roles for users.. please help!

• matt swift

  Newbie

  Points: 9

  More actions

  October 4, 2004 at 3:42 am

  #162828

  I have a problem here , I'm hoping someone can pass on some SQL knowledge to help me..

  I'm using dynamic sql SP's .  So I add parmaters to the SP dynamically by filters in the code.

  Now in this particular query, I want to check users for their roles.  I want to pass in a number of roleID's into the SP, and return users with those specific roles.

  Users can have a number of roles, obviously.  I've tried putting in a comma seperated list of roleid's and im now thinking to do the role id's seperated (pass in a max of 5 say).

   

  The problem is, if I say, put in 1 role id, the query will find the user with that role, but also it will pull back that user if they have many other roles, and I want it to pull back a user with ONLY that 1 role ID.

  I'm pulling back from a view which contains a lit of Users, their ID's ,and their RoleIDs, so if a User has many roles, they entry is repeated for each roleid.

  I know this probably sounds MEGA confusing.  I am, myself, confused

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  October 4, 2004 at 5:34 am

  #525176

  Ok.  Sounds like you need to do the following ADD:

  HAVING COUNT(AssociateID) = [the number of different role IDs you are looking for].

  This would ONLY pull back associates that have the 1 role ID or the 2 role IDs, etc..

  You can learn more about HAVING from BOL

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net