SQL query: get tags associated with a message

Question

I have three tables.

posts | id | title | +---------+-----------+ | 1 | hello | | 2 | goodbye | +---------+-----------+ posts_tags | tag_id | post_id | +---------+-----------+ | 1 | 1 | | 2 | 1 | | 2 | 2 | +---------+-----------+ tags | id | name | +---------+-----------+ | 1 | news | | 2 | photos | +---------+-----------+ 

I want to be able to select messages, but have it as a result

 post.id post.title tags ------------------------------------ 1 hello news,photos 2 goodbye photos 

Sort of

 SELECT *, GROUP_CONCAT(tags.name) AS tags FROM posts LEFT JOIN posts_tags ON posts.id = posts_tags.post_id LEFT JOIN tags ON posts_tags.tag_id = tags.id 

doesn't seem to be working properly. Please advise, thanks for your time :)

Answer 1

It would be better to store tags additionally in a line directly in the message table to prevent additional joining and grouping. Like denormalizing performance.

Answer 2

You need to add the GROUP BY to your request:

 SELECT posts.*, GROUP_CONCAT(tags.name ORDER BY tags.name) AS tags FROM posts LEFT JOIN posts_tags ON posts.id = posts_tags.post_id LEFT JOIN tags ON posts_tags.tag_id = tags.id GROUP BY posts.id 

I also added an order for GROUP_CONCAT above to get tags concatenated in the order you specified.