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 2 of 2
  1. #1
    Regular Coder
    Join Date
    Sep 2006
    Location
    India Mumbai
    Posts
    248
    Thanks
    13
    Thanked 1 Time in 1 Post

    extract distinct records

    Hello all
    Below is my table structure:

    Users_T
    User ID Username_VC
    1 Ron
    2 Allen
    3 Joes
    4 Harry


    Roles_T
    Role_ID RoleName_VC
    1 Admin
    2 Inspector
    3 Auditor
    4 Accountant

    Users_Roles_T
    User_ID Role_ID
    1 1
    1 2
    2 2
    3 1
    3 2
    3 3


    I want to know all the users who are just Inspectors and no other roles are assigned to them.As per the example above it should return me only name of Allen

    below is what i tried but fails:
    select u.user_id , ur.Role_ID , RoleName = dbo.uf_parmsel_rolename_of_role(ur.Role_ID)
    from users_t u , Users_Roles_T ur
    WHERE u.User_ID=ur.User_ID and ur.role_id = '2' and ur.role_id not in (1,3,4) order by u.user_id

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    well, this doesnt win a price for beauty, but i works nevertheless.

    Just do a select on all users (and join the rest of the tables) who are inspector and subtract all the users which do also have other roles..

    Code:
    SELECT *
    FROM Users_T u
    INNER JOIN Users_Roles_T ur
    ON u.[User ID] = ur.User_ID
    INNER JOIN Roles_T r
    ON ur.Role_ID = r.Role_ID
    WHERE (r.RoleName_VC = 'Inspector')
    AND (u.[User ID] NOT IN
           (SELECT     u.[user id]
            FROM Users_T u
            INNER JOIN Users_Roles_T ur
            ON u.[User ID] = ur.User_ID
            INNER JOIN Roles_T r
            ON ur.Role_ID = r.Role_ID
            WHERE (r.RoleName_VC <> 'Inspector')))
    I am the luckiest man in the world


  •  

    Posting Permissions

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