Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: extract distinct records
10-07-2008, 09:18 AM #1
- Join Date
- Sep 2006
- India Mumbai
- Thanked 1 Time in 1 Post
extract distinct records
Below is my table structure:
User ID Username_VC
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
10-17-2008, 08:06 AM #2
- Join Date
- Jun 2002
- Zwolle, The Netherlands
- 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..
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