How do I select one row in SQL when another row has same id but a different value in a column?-CodePudding

I am creating a website where you can share posts with multiple tags, now I encountered the problem that a post is shown multiple times, each one with one tag. In my database I have a table posts and a table tags where you link the post_id. Now my question is: how can I get only one post but multiple tags on this one post?

screenshot of query in database

CodePudding user response：

This should work, edit column names if needed:

SELECT *, 
(SELECT GROUP_CONCAT(DISTINCT tag) FROM tags WHERE post_id = posts.id) 
FROM posts

CodePudding user response：

You can use GROUP_CONCAT() in MySQL and string_agg() in MS SQL Server

SELECT posts.id,GROUP_CONCAT(tags.tag)
FROM posts
Left JOIN tags on tags.post_id = posts.id
GROUP BY posts.id