mysql - 连接多个表并返回零(如果不是排在一个表中)

Question

我正在尝试在 MySQL 中设置一个新的权限数据库，并且我正在为一些我确信非常简单的事情而绞尽脑汁。我确信之前已经在这里找到了一些与此曲调相关的答案，但经过几个小时的搜索后，我没有发现任何有效的东西。

我有 4 个相关的表格

权限(包含所有可能的权限)

|permission_name | description |
--------------------------------
|users.list      | etc. etc.   |
|users.update    | etc. etc.   |
|users.delete    | etc. etc.   |

用户

| id      | fname | group_id |
------------------------------
| 1       | John  | 1        |
| 2       | Nancy | 1        |
| 3       | Paul  | 2        |

组

| group_id | group_name |
-------------------------
| 1        | Webmasters |
| 2        | Corporate  |
| 3        | HR         |

Group_permission(包含与每个组相关的权限)

| group_id | permission_name | permission_type (1=Y|0=not set|-1=N)
----------------------------------------------
| 1        | users.list      | 1              |
| 1        | users.update    | 1              |
| 2        | users.list      | 1              |

好吧，有很多关系正在进行，但我正在尝试获取特定用户的所有组权限，即使组权限尚不存在。

我想象这是某种使用权限表作为基础的左连接，但每当我包含 WHERE user.id = 2 时，它都会限制我的结果集，并且不会在右侧包含空值。

SELECT a.permission_name, IFNULL(b.permission_type, 0)
FROM permission a
LEFT JOIN group_permission b on b.permission_name = a.permission_name
LEFT JOIN user c on c.group_id = b.group_id
WHERE c.id = 2

我想看到 Nancy 的结果是

|permission_name | permission_type |
------------------------------------
|users.list      | 1               |
|users.update    | 0               |
|users.delete    | 0               |

在 PHP 端我不知道用户属于哪个组，所以只能通过用户 ID 来查询。

我得到的只是

|permission_name | permission_type |
------------------------------------
|users.list      | 1               |

任何帮助表示赞赏。 TIA

Answer 1

它最终只是一个完成了任务的子查询。

SELECT a.permission_name, IFNULL(b.permission_type, 0)
FROM permission a
NATURAL LEFT JOIN 
(
    SELECT a.group_id, a.permission_name, a.permission_type FROM group_permission a
    NATURAL LEFT JOIN users b
    WHERE b.id = 2
) as b