extract distinct records

[bhakti_thakkar]

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

[Roelf]

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..

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')))