MySQL JOIN 在与 LEFT JOIN、WHERE 和 OR 组合时返回不相关的行

Question

我有以下表格结构。这个想法是用户可以通过他们的类别或特定的用户覆盖来访问论坛。 (在这两种情况下，'action' 都是一个值为 'read' 和 'write' 的枚举)

user (id, class)
forum (id, name)
forum_permissions (forum_id, class_id, action)
forum_user_permissions (forum_id, user_id, action)

通过以下查询，我根据 forum_permissions 中的行获得了我不期望的额外结果。我的意思是，即使 class_id 不匹配，也会返回 forum_permissions 中 forum_id = 3 的每一行。

SELECT      forum.id AS forum_id, forum.name
FROM        forum
JOIN        forum_permissions ON forum_permissions.forum_id = forum.id
LEFT JOIN   forum_user_permissions ON ( 
             forum_user_permissions.forum_id = forum.id AND forum_user_permissions.user_id = 3 )
WHERE       (( forum_permissions.class_id = 1 AND forum_permissions.action = 'read' )
              OR
             ( forum_user_permissions.action = 'read' ))

例如我明白了:

FORUM_ID    NAME
   1        chat
   2        support
   3        secret
   3        secret
   3        secret
   3        secret

但预期是这样的:

FORUM_ID    NAME
   1        chat
   2        support
   3        secret

我用包含数据的具体示例制作了一个 SQL Fiddle http://sqlfiddle.com/#!2/75c3a/5/0

Answer 1

您的左联接正在添加这些额外的行。 mybe 如果你改变 WHERE

WHERE  forum_user_permissions.user_id is not null and (
  (forum_permissions.class_id = 1 AND forum_permissions.action = 'read')
OR
  (forum_user_permissions.action = 'read')
)

或者

SELECT
 forum.id AS forum_id, forum.name
FROM
 forum
JOIN
 forum_permissions 
  ON
    forum_permissions.forum_id = forum.id
LEFT JOIN
 forum_user_permissions
  ON (
    forum_user_permissions.forum_id = forum.id 
  )
WHERE forum_user_permissions.user_id = 3 and (
 (forum_permissions.class_id = 1 AND forum_permissions.action = 'read')
 OR
 (forum_user_permissions.action = 'read')
 )

但这取决于你想要得到的结果