sqlsql-serverouter-join

How to get data from three joined tables, and always show data from 1st, and nulls or existsing records from 3rd?


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

Solution

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

    db<>fiddle