Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Sep 2006
    Location
    India Mumbai
    Posts
    248
    Thanks
    13
    Thanked 1 Time in 1 Post

    Mssql 2005 - help in query building

    Hi all,
    i have a table (Users_Roles_T) where in i assign different roles to a user for e.g user_id 1 has three roles 1,2,5 (Admin , Accounts , Inspector)

    Users_Roles_T :
    User_ID | ROle_ID | RoleName_VC
    1 1 Admin
    1 2 Accounts
    1 5 Inspector

    Now there is another table in which i store all the menus that are applicable for a user

    for eg : Menu_T
    Menu_ID | MenuName_VC | Roles_ID (storing comma seperated in this field)
    1 Sales 1,5,4,2,
    2 New projects 5,4,9,3,
    3 Registrations 8,12,10


    in the above eg Menu Sales and New Projects both are available to user "1" as his combined roles have the respective menus. But Registration Menu is not applicable to user "1" as it doesnt fall in to any roles of this user(1,2,5)

    How will i build the query to extract all the MEnu_ID from Menu_T table for a Role
    Please help
    Thanks
    Bhakti
    Last edited by bhakti_thakkar; 07-07-2009 at 12:22 PM.

  • #2
    Senior Coder ckeyrouz's Avatar
    Join Date
    Jun 2009
    Location
    Montreal, Canada
    Posts
    1,044
    Thanks
    5
    Thanked 179 Times in 179 Posts
    select distinct m.Menu_ID
    from Menu_T m, Users_Roles_T u
    where m.roles_id = u.roles_id
    and u.user_id = 1

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,193
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Wrong answer from CKeyrouz.

    Because Bhakti is making the bad MISTAKE of using a delimited list in a DB field, the query CKeyrouz gave will never find any matches, unless one Roles_ID just happens to only have a single number.

    CKeyrouz can be forgiven for expecting a GOOD db design instead of this mistake.

    Bhakti: You *really* need ANOTHER TABLE in your DB.

    You SHOULD have something like this:
    Code:
    TABLE : Menu_T
    Menu_ID | MenuName_VC 
       1    | Sales
       2    | New projects 
       3    | Registrations 
    
    TABLE: MenuRoles
    Menu_ID | Role_ID
       1    |   1
       1    |   5
       1    |   4
       1    |   2
       2    |   5
       2    |   4
       etc.
    *NOW* CKeyrouz's answer is correct, if we just change the table name:
    Code:
    select distinct m.Menu_ID
    from MenuRoles m, Users_Roles_T u
    where m.role_id = u.role_id
    and u.user_id = 1
    Using a delimited list in a single DB field is almost always a mistake. Clearly it is a mistake in this case.

  • #4
    Senior Coder ckeyrouz's Avatar
    Join Date
    Jun 2009
    Location
    Montreal, Canada
    Posts
    1,044
    Thanks
    5
    Thanked 179 Times in 179 Posts
    I did not pay attention to the fact that the values are comma separated.
    I apologize for that.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,193
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    CKeyrouz: Patently not your fault. You wouldn't expect a mistake like that!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •