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

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

sql


Solution 1:[1]

This should work, edit column names if needed:

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Moebius
Solution 2 thisisnabi

Related Questions