sql join Many-To-Many - 3 tables

Question

I have 3 tables in sqlserver :

tbl_Users            -  User_ID, User_Name  
tbl_Roles            -  Role_ID, Role_Name  
tbl_Users_Roles_MTM  -  User_ID, Role_ID

A user can have multiple roles assigned to him, and that will show in the Many-To-Many table.

In my stored-procedure I need Role_Name which are NOT assigned to a specific User_ID (which is given as a parameter).
I guess I should use an INNER JOIN (or a LEFT one...).

There are numerous entries in SO and other forums with questions nearly similar to this but not quite. I experimented a lot but by now I completely lost my hands and feet!

Thank you all.

EDIT :
With the help of the good people of SO, I got it to work :

SELECT r.Role_Name
       FROM tbl_Roles r
       WHERE NOT EXISTS(
             SELECT 1 
             FROM tbl_Users_Roles_MTM ur
             WHERE ur.User_ID = @User_ID
             AND   ur.Role_ID = r.Role_ID);

SO people are awesome!!!!!

Answer 1

Try this query:

SELECT r.Role_Name
FROM tbl_Roles r
WHERE NOT EXISTS (
    SELECT 1 
    FROM tbl_Users_Roles_MTM ur
    WHERE ur.User_ID = @User_ID
    AND ur.Role_ID = r.Role_ID);