Rewrite Subquery(not in) as Join

Question

Hi all is it possible to rewrite query:

select userid from User where userid not in(select userid from UserRole where roleid in(8));

as Join?

the problem is that one user may have several roles
thank you in advance.

mysql> desc User;  
+--------------------+-------------------+------+-----+---------+----------------+  
| Field              | Type              | Null | Key | Default | Extra          |  
+--------------------+-------------------+------+-----+---------+----------------+  
| userId             | int(11)           | NO   | PRI | NULL    | auto_increment |  
| userName           | varchar(50)       | YES  |     | NULL    |                |  

...and other user related columns

mysql> desc UserRole;  
+--------+---------+------+-----+---------+-------+  
| Field  | Type    | Null | Key | Default | Extra |  
+--------+---------+------+-----+---------+-------+  
| userId | int(11) | NO   | PRI | 0       |       |  
| roleId | int(11) | NO   | PRI | 0       |       |  
+--------+---------+------+-----+---------+-------+  

Answer 1

Maybe this will work?

select userid from User 
left outer join
(select userid, roleid from UserRole where roleid in(8)) v
on User.userid = v.userid
where roleid is null;

Answer 2

I think this should get you everyone who has a Role other than 8

select userid from User
where not exists 
  (select UserId from UserRole
     where UserRole.userid = User.userid and roleid not in(8) )

Answer 3

If you want to go for readability you could use the EXCEPT clause.

I.e.

select userId from user 
except
select userId from UserRole where roleId <> 8

Not sure if MySQL supports "Except" though.

Answer 4

I haven't tested this, but I think it works.

select userID from user 
left join UserRole 
on user.userID = UserRole.userID 
and UserRole.roleID = 8
where UserRole.roleID IS NULL