如何实现多表关联并将指定角色名按逗号分隔，过滤Role_id<30的结果

数据表结构 #

Tbl_user_vs_roles #

Staff_id    Role_id
111         10
111         20
111         30
222         20
333         30

Tbl_roles #

Role_id     Role_name
10          Operator
20          Manager
30          Head

Tbl_user_details #

Staff_id   Staff_name    Phone_no
 111       Niya          12345678
 222       Ram           12345677
 333       Varun         12345688

需求 #

关联三张表，将Role_id < 30的角色名按逗号分隔展示，仅保留有符合条件角色的员工，预期输出如下：

Staff_id         Staff_name     Roles                   Phone_no
   111            Niya         Operator,Manager         12345678
   222            Ram          Manager                   12345677

尝试的SQL语句 #

SELECT Tbl_user_details.Staff_id, Tbl_user_details.Staff_name,
Tbl_user_details.Phone_no,STUFF((SELECT ',' + RTRIM(j.Role_name) FROM Tbl_roles j  JOIN Tbl_user_vs_roles
k ON j.Role_id = k.Role_id WHERE Tbl_user_details.Staff_id = k.Staff_id AND k.Role_id <30 ORDER BY j.Role_name FOR XML PATH('')),1,1,'') AS 'Roles'  FROM Tbl_user_details
group by Tbl_user_details.Staff_id,Tbl_user_details.Staff_name, 
Tbl_user_details.Phone_no

遇到的问题 #

执行后结果包含了无符合条件角色的员工（如Staff_id=333，Roles为NULL），输出如下：

Staff_id        Staff_name     Roles                   Phone_no
   111            Niya         Operator,Manager         12345678
   222            Ram          Manager                   12345677
   333            Varun        NULL                     12345688          

修改方案 #

可以通过两种方式过滤掉无符合条件角色的员工：

方案1：用EXISTS子句提前判断 #

在原SQL中添加WHERE EXISTS条件，直接筛选出有符合条件角色的员工，避免后续出现NULL行：

SELECT 
    ud.Staff_id, 
    ud.Staff_name,
    ud.Phone_no,
    STUFF((
        SELECT ',' + RTRIM(r.Role_name) 
        FROM Tbl_roles r  
        JOIN Tbl_user_vs_roles ur ON r.Role_id = ur.Role_id 
        WHERE ud.Staff_id = ur.Staff_id AND ur.Role_id < 30 
        ORDER BY r.Role_name 
        FOR XML PATH('')
    ), 1, 1, '') AS Roles
FROM Tbl_user_details ud
WHERE EXISTS (
    SELECT 1 
    FROM Tbl_user_vs_roles ur 
    WHERE ur.Staff_id = ud.Staff_id AND ur.Role_id < 30
)
GROUP BY ud.Staff_id, ud.Staff_name, ud.Phone_no

方案2：先关联过滤再聚合 #

通过JOIN先获取有符合条件角色的员工记录，再进行字符串聚合，天然过滤掉不符合条件的员工：

SELECT 
    ud.Staff_id, 
    ud.Staff_name,
    ud.Phone_no,
    STUFF((
        SELECT ',' + RTRIM(r.Role_name) 
        FROM Tbl_roles r  
        JOIN Tbl_user_vs_roles ur ON r.Role_id = ur.Role_id 
        WHERE ud.Staff_id = ur.Staff_id AND ur.Role_id < 30 
        ORDER BY r.Role_name 
        FOR XML PATH('')
    ), 1, 1, '') AS Roles
FROM Tbl_user_details ud
JOIN Tbl_user_vs_roles ur ON ud.Staff_id = ur.Staff_id
WHERE ur.Role_id < 30
GROUP BY ud.Staff_id, ud.Staff_name, ud.Phone_no

内容的提问来源于stack exchange，提问作者user16694658