SQL query: get tags associated with post

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 the posts, but have this as a result

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

Something like

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 work properly. Please advise, thanks for your time :)

Answer 1

You need to add a GROUP BY clause to your query:

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 to the GROUP_CONCAT above to get the tags concatenated in the order you specified.

Answer 2

The better will be storing tags additionally in a string right in posts table too to prevent additional joins and grouping. Just as performance denormalization.