Filter SQL INNER JOIN with more results for a RS (user can have more roles)

Question

I have a question about SQL. Following:

I have 2 tables, a User and a Role table. A User can have several roles. A default-user has only one role (id=1), a admin two (id=1 & 2).

Now I would like to return only the users, who didn't own the admin-role (id=2). But with my current query, I receive all users, because the admins owned the roleID 1 (default-user) as well.

So I hope that anyone can solve my problem.

My current query:

SELECT * FROM (SELECT u.userID AS uID, u.firstName, u.lastName, r.roleID AS rID 
FROM User AS u INNER JOIN roleUser AS ru INNER JOIN Role AS r) 
WHERE rID = 1 //--> dont know to handle this..

Answer 1

Get all users without admin role associated

Try this:

SELECT * FROM Users u
WHERE NOT EXISTS(
    SELECT 'ADMIN'
    FROM RoleUser ru
    WHERE ru.rID = 2
   AND ru.userID = u.userID)

If you want to envolve Role table:

SELECT * FROM Users u
WHERE NOT EXISTS(
    SELECT 'ADMIN'
    FROM RoleUser ru
    JOIN Role r
        ON r.roleID = ru.roleID
    WHERE r.name = 'ADMIN'
   AND ru.userID = u.userID)