MySQL三个表链接左连接，按最后一个表过滤

Question

我正在尝试找到一种有效的方法来过滤掉由链接的三个表左连接生成的结果集，其中第二个表连接将考虑第三个表的属性。

小提琴：http://sqlfiddle.com/#!2/e319e/2/0

一个简化的示例是三个表之间的联接：帖子、评论和作者。帖子可以有 0..N 条由作者撰写的评论。我想获得仅由活跃作者撰写的所有帖子+活跃评论的列表。

考虑以下数据：

发布：

| id |       title |
|----|-------------|
|  1 |  First post |
|  2 | Second post |
|  3 |  Third post |
|  4 |  Forth post |

作者：

| id |                  title | is_active |
|----|------------------------|-----------|
|  1 |           First author |         1 |
|  2 |          Second author |         1 |
|  3 |           Third author |         1 |
|  4 | Fourth inactive author |         0 |
|  5 |  Fifth inactive author |         0 |

注释：

| id | post_id | author_id |                  title | is_active |
|----|---------|-----------|------------------------|-----------|
|  1 |       1 |         1 |          First comment |         1 |
|  2 |       2 |         1 |         Second comment |         1 |
|  3 |       1 |         2 |          Third comment |         1 |
|  4 |       2 |         4 |         Fourth comment |         1 |
|  5 |       2 |         5 | Fifth inactive comment |         0 |
|  6 |       2 |         3 | Sixth inactive comment |         0 |
|  7 |       4 |         4 |        Seventh comment |         1 |

现在执行简单的过滤查询：

SELECT
  p.id post_id, p.title post_title,
  c.id as comment_id, c.title comment, c.is_active active_comment,
  a.id author_id, a.title author, a.is_active active_author
FROM Post p
LEFT JOIN Comment c ON c.post_id = p.id AND c.is_active = 1
LEFT JOIN Author a ON a.id = c.author_id AND a.is_active = 1
ORDER BY p.id;

为我们带来以下结果集：

| id |       title |     id |           title | is_active |     id |         title | is_active |
|----|-------------|--------|-----------------|-----------|--------|---------------|-----------|
|  1 |  First post |      1 |   First comment |         1 |      1 |  First author |         1 |
|  1 |  First post |      3 |   Third comment |         1 |      2 | Second author |         1 |
|  2 | Second post |      2 |  Second comment |         1 |      1 |  First author |         1 |
|  2 | Second post |      4 |  Fourth comment |         1 | (null) |        (null) |    (null) |
|  3 |  Third post | (null) |          (null) |    (null) | (null) |        (null) |    (null) |
|  4 |  Forth post |      7 | Seventh comment |         1 | (null) |        (null) |    (null) |

其中有两个评论应该省略 - 由不活跃的作者撰写的"第四条评论"和"第七条评论"。

我认为唯一可行的方法是为注释添加 JOIN 条件

AND c.id IN (SELECT id FROM Author WHERE is_active = 1)

这会产生一个正确的结果集，但我想不是很理想。但是我找不到任何其他可行的解决方案。有没有办法以某种方式优化它？谢谢！

Answer 1

我认为你想要这个from子句：

FROM Post p LEFT JOIN
     (Comment c JOIN
      Author a
      ON a.id = c.author_id AND a.is_active = 1 and c.is_active = 1
     )
     ON c.post_id = p.id

但是，正如我在评论中提到的，您可能希望停用不活跃作者的评论。 这将涉及触发器或存储过程。

哦，你很有礼貌地放了一个SQL小提琴，所以它在这里工作。

Answer 2

如果你也想省略"第三篇文章"（仅限活跃作者），你可以试试这个：

 SELECT 
  p.id post_id, p.title post_title,
  c.id as comment_id, c.title comment, c.is_active active_comment,
  a.id author_id, a.title author, a.is_active active_author
 FROM Post p
 LEFT JOIN Comment c ON c.post_id = p.id 
 LEFT JOIN Author a ON a.id = c.author_id
 WHERE c.is_active = 1 AND a.is_active = 1
 ORDER BY p.id;

Answer 3

这也

应该这样做：

select a.title, p.title, c.title 
from author a 
left join comment c on a.id = c.author_id
left join post p on c.post_id = p.id
where a.id in (select id from author where is_active = 1) 
and c.is_active = 1

http://sqlfiddle.com/#!2/e319e/1