左连接返回空值后的内连接

Question

我想返回每个p行，并为u.id和u.first_name提供空值，如果它们没有ROLE_ADMIN。当我执行此查询时，我得到空结果。如果我使用左连接而不是内，我得到不正确的结果。如何过滤左连接，以便如果没有匹配，我仍然得到返回的p行与u.id和u.first_name的空值？

注：我在末尾添加了p.id=13455，仅用于测试目的。

select u.id, u.first_name, p.id
from sub p
    left join oub oj on oj.id = p.sub_id
    left join jhi_user u on u.oub_id= oj.id 
    inner join jhi_user_authority ua on ua.user_id = u.id where ua.authority_name = 'ROLE_ADMIN' and p.id = 13544;

Answer 1

所以我这样解决了：

select u.id, u.first_name, p.id
from sub p
    left join oub oj on oj.id = p.sub_id
    left join jhi_user u on u.id =
    (select ru.id from jhi_user ru inner join jhi_user_authority rua on rua.user_id = ru.id and rua.authority_name = 'ROLE_ADMIN' where ru.oub_id= oj.id)
    where p.id = 18774;

Answer 2

我认为你想要的逻辑是：

select 
    ua.user_id,
    case when ua.user_id is not null then u.first_name end as first_name,
    p.id
from sub p
left join oub oj 
    on oj.id = p.sub_id
left join jhi_user u 
    on u.oub_id= oj.id 
left join jhi_user_authority ua 
    on ua.user_id = u.id
    and ua. authority_name = 'ROLE_ADMIN'
    and u.id is not null

Answer 3

像下面一样使用左连接

select u.id, u.first_name, p.id
from sub p
    left join oub oj on oj.id = p.sub_id
    left join jhi_user u on u.oub_id= oj.id 
    left join 
   ( select * from
jhi_user_authority where authority_name = 'ROLE_ADMIN' 
)  ua on ua.user_id = u.id
  where p.id = 13544;