mysql - 内部加入 3 个表以获得权限

Question

嘿，我正在尝试用表 user 中的所有记录填充我的查询，并让每一个检查以查看他们在其他 2 个表中的权限(user_permission 和 permission)。

我目前的查询是这样的:

SELECT user.id, user.email,  
CONCAT(' ', user.name, user.username) AS theNameUserName,
IF(user.opted_in = 0, 'NO', 'YES') AS optedIn  
FROM user AS user, user_permission AS userPerm
INNER JOIN permission AS Perm ON Perm.id = userPerm.permission_id;

它执行没有问题，但一遍又一遍地只有相同的数据。它只返回了 1000 条记录，并且每个人重复了 3 次。我在用户表中有470个用户，所以我应该返回所有470条记录，这也告诉我他们的许可。

我需要对上面的查询做些什么才能让它做到这一点？ User.id 将与 user_permission user_id 匹配。

user_permission 表布局:

  user_id  permission_id
       1        1
       2        3
       3        3
       8        4
       9        4
      10        4
      11        4
      12        4
      13        4
       ....etc etc

权限表:

   id            name
    4      registered users
    3      editor
    2      developer
    1      administrator
    5      registered builders

用户表:

   id      name         email                username           opted_in
   11   David Dxxxx     dxxxxx@exxxx.com    dxxxxxx@exxxxx.com      0
   12   Michael Lxx     sx@sprintmail.com   sxxxxxx@sxxxxx.com      0
   13   Jon Fxxxxxx     jxxxxxxx@xxx.com    jxxxxxx@sxxxxx.com      0
        etc etc .......

Answer 1

你有隐式(逗号分隔)INNER JOIN 和显式的混合，最终你没有条件将 user 加入 user_permission 这就是为什么你得到两者的笛卡尔积。您的 JOIN 应如下所示:

SELECT
  user.id,
  user.email,  
  CONCAT_WS(' ', user.name, user.username) AS theNameUserName,
  IF(user.opted_in = 0, 'NO', 'YES') AS optedIn  
FROM 
  user
  LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id
  INNER JOIN permission AS Perm ON Perm.id = userPerm.permission_id;

我在 user 和 user_permission 之间替换了一个 LEFT JOIN 以将没有相关权限的用户列为 NULL .如果应从结果中省略它们，请改用 INNER JOIN。

我还用 CONCAT_WS() 替换了你的 CONCAT() 因为你首先有空间 - 你只会在字符串的前面加上一个空格而不是而不是将两者分开(如果这是您的意图)。

最后一点:SELECT 中的 IF() 条件适用于 MySQL 和其他一些 RDBMS，但最便携的版本将使用 CASE 改为:

CASE WHEN user.opted_in = 0 THEN 'NO' ELSE 'YES' END AS theNameUserName

...如果您希望在不同的 RDBMS 上运行它，这只是一个建议。