3个表的MySQL JOIN

Question

我有 3 个表，我需要它们的组合才能看到所有列的组合 (MySQL)。

第一。表 poi_ACL_permissions

 ID        permKey          permName
|1 |access_site           |Access Site
|2 |access_admin          |Access Admin System
|3 |publish_articles      |Publish Articles
|4 |publish_events        |Publish Events
|5 |install_modules       |Install Modules
|6 |post_comments         |Post Comments
|7 |access_premium_content|Access Premium Content
|8 |limited_admin         |Limited Admin

第二。表是 poi_ACL_roles

 ID   roleName
| 1|Administrators
| 2|All Users
| 3|Authors
| 4|Premium Members

第三。表是 poi_ACL_role_perms

  ID  roleID  permID  value     addDate
| 16|      1|      2|     1|2009-03-02 17:13:21
| 17|      1|      7|     1|2009-03-02 17:13:21
| 18|      1|      1|     1|2009-03-02 17:13:21
| 19|      1|      5|     1|2009-03-02 17:13:21
| 20|      1|      8|     1|2009-03-02 17:13:21
| 21|      1|      6|     1|2009-03-02 17:13:21
| 22|      1|      3|     1|2009-03-02 17:13:21
| 23|      1|      4|     1|2009-03-02 17:13:21
| 24|      2|      1|     1|2009-03-02 17:13:31
| 25|      3|      7|     1|2009-03-02 17:13:38
| 26|      3|      8|     1|2009-03-02 17:13:38
| 27|      3|      6|     1|2009-03-02 17:13:38
| 28|      3|      3|     1|2009-03-02 17:13:38
| 29|      3|      4|     1|2009-03-02 17:13:38
| 30|      4|      7|     1|2009-03-02 17:13:42
| 31|      4|      6|     1|2009-03-02 17:13:42

我有这个问题:

SELECT [roleName], [roleID], [value], [permID] AS RPID, [permKey], [permName], [poi_ACL_permissions].[ID] AS PID
FROM [poi_ACL_permissions] 
LEFT OUTER JOIN [poi_ACL_role_perms] 
ON [poi_ACL_permissions].[ID] = [poi_ACL_role_perms].[permID]
LEFT OUTER JOIN [poi_ACL_roles] 
ON [poi_ACL_role_perms].[roleID] = [poi_ACL_roles].[ID]         
ORDER BY [roleID], [permID]  ASC 

事实上，它给了我我需要的东西......(几乎;))结果是包含所有角色及其权限的数组。但是，我还需要 NULL 权限。

现在:

0   Administrators  1   1   1   access_site     Access Site     1
1   Administrators  1   1   2   access_admin    Access Admin System     2
2   Administrators  1   1   3   publish_articles    Publish Articles    3
3   Administrators  1   1   4   publish_events  Publish Events  4
4   Administrators  1   1   5   install_modules     Install Modules     5
5   Administrators  1   1   6   post_comments   Post Comments   6
6   Administrators  1   1   7   access_premium_content  Access Premium Content  7
7   Administrators  1   1   8   limited_admin   Limited Admin   8
8   All Users       2   1   1   access_site     Access Site     1
9   Authors         3   1   3   publish_articles    Publish Articles    3
10  Authors         3   1   4   publish_events  Publish Events  4
11  Authors         3   1   6   post_comments   Post Comments   6
12  Authors         3   1   7   access_premium_content  Access Premium Content  7
13  Authors         3   1   8   limited_admin   Limited Admin   8
14  Premium Memb.   4   1   6   post_comments   Post Comments   6
15  Premium Memb.   4   1   7   access_premium_content  Access Premium Content  7

但是，例如所有用户都应该:

0   All Users   2   1   1   access_site
1   All Users   2   0   2   access_admin
2   All Users   2   0   3   publish_articles
3   All Users   2   0   4   publish_events
4   All Users   2   0   5   install_modules
5   All Users   2   0   6   post_comments
6   All Users   2   0   7   access_premium_content
7   All Users   2   0   8   limited_admin

等...

你能帮我处理那个 SQL 吗？

Answer 1

在左连接到 role_permission 表之前，在您的 role 和 permission 表之间使用一个CROSS JOIN:

SELECT 
  r.roleName, 
  r.ID AS roleID, 
  COALESCE(rp.value, 0) AS value, 
  p.ID AS permissionID,
  p.permKey, 
  p.permName 
FROM poi_ACL_permissions p
CROSS JOIN poi_ACL_roles r
LEFT OUTER JOIN poi_ACL_role_perms rp
  ON p.ID = rp.permID
  AND r.ID =  rp.roleID
ORDER BY r.ID, p.ID

另外，请注意表别名(r、p、rp)的使用，这有助于提高查询的可读性。

SQL Fiddle