我有3个表,我需要它们的组合来查看所有列的组合(MySQL)。
1.表poi_ACL_权限

 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

事实上,这给了我所需要的。。。(几乎;))结果是具有所有角色及其权限的数组。但是,我需要的也是空权限。
现在:
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吗?

最佳答案

在您的CROSS JOINrole表之间使用permission,然后左键加入role_permission表:

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

另外,请注意表别名的使用(rprp)有助于查询的可读性。
SQL Fiddle

09-17 14:02