I have three tables, Roles, Users, and connecting table. I want to show all roles, and existence of it for particular user. Basically a table with roles, which check box "in role" on the page "User Roles".
I'm trying with left outer join, see my solution below, could someone help to simplify it? Is it possible to do without subquery inside of join condition? For SQL Server please.
create table tRole (id int, name varchar(255))
insert into tRole values (1,'user')
insert into tRole values (2,'admin')
create table tRoleUser (roleId int, userId int)
insert into tRoleUser values (1,1) -- user john
insert into tRoleUser values (1,2) -- user sam
insert into tRoleUser values (2,2) -- admin sam
-- amanda not user, and not admin
create table tUser (id int, name varchar(255))
insert into tUser values (1,'john')
insert into tUser values (2,'sam')
insert into tUser values (3,'amanda')
select * from tRole
select * from tRoleUser
select * from tUser
Proposed solution:
select
r.name 'role name'
,CASE WHEN ru.userid IS NULL THEN 'FALSE' ELSE 'TRUE' END UserInRole
--,ru.userid
--,(select name from tuser where id = ru.userid) 'user name'
from tRole r
left outer join tRoleUser ru on
r.id = ru.roleid
and (ru.userid = (select id from tUser where name = 'john') or ru.roleid is null)
Result for john:
user TRUE
admin FALSE
Result for amanda:
user FALSE
admin FALSE
Result for sam:
user TRUE
admin TRUE
You are overcomplicating it. It's just two joins, and make sure to put conditions on the join tables into the ON not the WHERE.
Furthermore, the inner join on tUser needs to be nested inside the left join for tRoleUser
select
r.name [role name],
ru.userid,
u.name [user name]
from tRole r
left join tRoleUser ru
join tUser u on u.id = ru.userid and u.name = 'john'
on r.id = ru.roleid;