sql - 双外连接

Question

我有一个表 People、一个表 Permission 和一个表 Action。允许在 People 和 Action 之间提供多对多关系。

我的目标很简单:我想在 People 中寻找一个人，我想查看 Permission 中列出的权限，以及所有可用的操作，无论此人是否具有权限。

请注意，People 的成员不必在 Permission 中表示。因此，只有在获得数据库中写入的许可后，“John”才被允许做某事。

理想情况下，结果应该是这样的:

+-------------------------------------------+
+ People.name | Action.name        |Allowed |
+-------------------------------------------+
| John Doe    | Launching missiles | N      |
| John Doe    | Deleting code      | Y      |
+-------------------------------------------+

在开始之前，我只是想用每个数据构建一个简单的查询。获取某人的权限列表，即使他不在权限表中也很容易:

SELECT * FROM people pp LEFT OUTER JOIN permission pr ON pp.id = pr.people_id 
WHERE pp.name = 'John';

获取权限和操作列表很容易:

SELECT * FROM permission pr LEFT OUTER JOIN action a ON pr.action_id = a.id

但是，我似乎无法执行双外部连接来获得我想要的一切:

SELECT * FROM people pp LEFT OUTER JOIN permission pr ON pp.id = pr.permission_id
   LEFT OUTER JOIN people WHERE pp.name = 'John'

向 John 展示他没有获得许可的事实......但也没有采取任何行动。

换句话说，结果看起来像这样:

+-----------------------------------------+
+ Name    | People_id | Action_id |Action |
+-----------------------------------------+
| John    |           |           |       |
+-----------------------------------------+

当我想要的时候:

+-----------------------------------------------------------+
+ Name    | People_id | Action_id |Action                   |
+-----------------------------------------------------------+
| John    |           |           | Launching missiles      |
| John    |           |           | Deleting codes          |
| John    |           |           | (every other actions)   |
+-----------------------------------------------------------+

那么，我想要的是可能的吗？如果是这样，我该如何实现？ (我正在使用 PostgreSQL)。

编辑:这是一个sqlfiddle:http://sqlfiddle.com/#!2/6d502

Answer 1

SELECT pp.name, pr.id_people, pr.id_action, a.name as action_name
 FROM people pp
 CROSS JOIN action a
 LEFT JOIN permission pr
   ON pr.id_people = pp.id
  AND pr.id_action = a.id

SQLFiddle:http://sqlfiddle.com/#!15/5a7b1/4

要按人名过滤添加到查询:

WHERE pp.name = 'John'