mysql 使用 GROUP CONCAT 和 JOIN 选择多行

Question

这是我一直在努力解决的问题。我需要从特定的博客 ID 中提取所有帖子。此外，我需要提取属于每个帖子的所有标签。

问题是，当帖子没有标签时，查询不会返回它。我怎样才能得到标签单元格的空结果而不是不返回行？

我有以下表格

帖子:

ID  title
74  HELLO
75  HELLO2
76  HELLO3

标签关系:

ID|post_id|tag_id
57  74       3
56  74       61
58  75       62
59  75       63

标签:

ID  tag
3   Love
61  Sometag
62  Sometag1
63  Sometag2
64  Sometag3
65  Sometag4

这是我的查询:

SELECT post . * ,
   user.username,
   user.picture,
   GROUP_CONCAT(tag.tag SEPARATOR ',') AS tags
FROM `posts` AS post
INNER JOIN `users` AS USER ON USER.blog_id = post.blog_id
INNER JOIN `tags_relation` AS tags_relation
INNER JOIN `tags` AS tag ON tag.ID = tags_relation.tag_id
WHERE post.blog_id =1
  AND post.status = 'publish'
  AND post.ID = tags_relation.post_id
GROUP BY post.ID
ORDER BY post.timestamp DESC LIMIT 0,10;

Answer 1

试试这个:

SELECT p.* ,
   u.username,
   u.picture,
   GROUP_CONCAT(t.tag SEPARATOR ',') AS tags
FROM `posts` AS p
INNER JOIN `users` AS u ON u.blog_id = p.blog_id
LEFT JOIN `tags_relation` AS tr ON p.ID = tr.post_id
LEFT JOIN `tags` AS t ON t.ID = tr.tag_id
WHERE p.blog_id =1
  AND p.status = 'publish'
GROUP BY p.ID
ORDER BY p.timestamp DESC LIMIT 0,10;