列出表A中的每条记录，并检查表B中是否存在相应的ID？

bike_guy

我有三个表：用户，组和成员。

Users
+---------+-----------+
| USER_ID | USER_NAME |
+---------+-----------+
|       1 | Bob       |
|       2 | John      |
+---------+-----------+

Groups
+----------+---------------+
| GROUP_ID |  GROUP_NAME   |
+----------+---------------+
|        1 | Administrator |
|        2 | Tester        |
|        3 | User          |
+----------+---------------+

Members
+---------+----------+
| USER_ID | GROUP_ID |
+---------+----------+
|       1 |        1 |
|       1 |        2 |
|       1 |        3 |
|       2 |        2 |
|       2 |        3 |
+---------+----------+

我正在尝试建立一个查询以返回每个GROUP_NAME的列表，如果指定用户是否属于该组，则返回TRUE或FALSE。

Bob的预期结果将是：

+---------------+--------+
|  GROUP_NAME   | MEMBER |
+---------------+--------+
| Administrator | TRUE   |
| Tester        | TRUE   |
| User          | TRUE   |
+---------------+--------+

John的预期结果将是：

+---------------+--------+
|  GROUP_NAME   | MEMBER |
+---------------+--------+
| Administrator | FALSE  |
| Tester        | TRUE   |
| User          | TRUE   |
+---------------+--------+

这是我用于查询的sudo代码：

SELECT GROUP_NAME, (TRUE or FALSE if USER_ID has a row with GROUP_ID) AS MEMBER
FROM Groups
JOIN Members
WHERE USER_ID = 1

你们有什么感想？

pwilcox

尝试进行交叉联接以获取所有用户/组组合，然后左联接并使用条件以确定是否存在：

declare @userId int = 2;

select      u.user_name,
            g.group_name,
            member = iif(m.user_id is null, 'false', 'true')
from        users u
cross join  groups g
left join   members m 
               on m.user_id = u.user_id
               and m.group_id = g.group_id
where       (u.user_id = @userId or @userId is null)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。