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
michal
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
Tony andrews
source
to share
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
Zak
source
to share
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
cindi
source
to share
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
Soraz
source
to share