Clever Geek Handbook

Rewrite subquery (not in) as Join

Hello everyone, you can rewrite the request:

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

as Join?

the problem is that one user can have multiple roles
thanks 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 | |  
+ -------- + --------- + ------ + ----- + --------- + ------- +
0


source to share


4 answers


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;
0


source


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
+1


source


I think this should give you everyone who has a role except 8

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


source


If you want to go reading, you can use the EXCEPT clause.

those.

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

Not sure if MySQL supports "Except".

0


source






More articles:


All Articles