选择所有可能的行，即使 JOIN 上不存在值

Question

我试图创建一个权限系统，但我无法选择我在数据库上的所有可能选项。例如，考虑到我的数据库中有以下表:

tb_group
id | group | slug
1 | Admin | admin
2 | Manager | manager
3 | Store Manager | store-manager
4 | Secretary | secretary
tb_area
id | area
1 | products
2 | users
3 | categories
4 | purchases
tb_permission
id_store | id_group | id_area | access | add | edit | remove
2        | 2        | 1       | 1      | 0   | 0    | 0
2        | 2        | 3       | 1      | 1   | 1    | 0

我想要的预期结果是从一个特定的组中获得所有可能的区域的所有可能的权限(访问、添加、编辑、删除)。所以我将得到这个最终结果:

id_store | id_group | area       | access | add | edit | remove
2        | 2        | products   | 1      | 0   | 0    | 0
2        | 2        | users      | 0      | 0   | 0    | 0
2        | 2        | categories | 1      | 1   | 1    | 0
2        | 2        | purchases  | 0      | 0   | 0    | 0

因此，即使我在表tb_permission上没有2组和2和4区域的任何寄存器，我也希望在查询结果中使用null或0值。

这是我目前的代码，但它只显示结果，如果区域存在于tb_permission表。

SELECT
tp.access, tp.add, tp.edit, tp.remove,
ta.area,
tg.slug
FROM tb_permission tp
LEFT JOIN tb_area ta
ON ta.id = tp.id_area
LEFT JOIN tb_group tg
ON tg.id = tp.id_group
WHERE tp.id_store = 2

我想获得组号2拥有的所有权限以及这个组在数据库中没有注册的所有权限。但我的查询只返回那些已经在数据库上。是否有可能得到我期望的结果与一个单一的查询?

Answer 1

你需要用tp_permission代替LEFT JOIN。将tp.id_store = 2的条件放在ON的条件中，这样它就不会过滤掉不匹配的行。

您可以CROSS JOIN组和区域表，以获得所有的组合。

SELECT 2 AS id_store, tg.id AS id_group, ta.area,
IFNULL(tp.access, 0) AS access, 
IFNULL(tp.add, 0) AS add,
IFNULL(tp.edit, 0) AS edit, 
IFNULL(tp.remove, 0) AS remove
FROM tb_group AS tg
CROSS JOIN tb_area AS ta
LEFT JOIN tb_permission AS tp
ON ta.id = tp.id_area AND tg.id = tp.id_group AND tp.id_store = 2
WHERE tg.id = 2