mysql select multiple rows using GROUP CONCAT and JOIN

Question

here's a problem I've been struggling with. I need to pull out all the posts from a spesific blog ID. In addition I need to pull out all the tags that belongs to each post.

The problem is that when a post have no tags the query doens't return it. How can I get an empty result for the tags cell instead of not returing the row?

I have the following tabels

posts:

ID  title
74  HELLO
75  HELLO2
76  HELLO3

tags_relation:

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

tags:

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

And here's my query:

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

Try this:

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;