mysql: get all rows into 1 column

Question

hi, i have 3 tables:

• post (id_post, title)
• tag (id_tag, name)
• post_tag (id_post_tag, id_post, id_tag)

Lets suppose that id_post 3 has 4 linked tags 1,2,3,4 (soccer, basket, tennis and golf).

Is there a way to return something like this in ONE row?

• col 1 id_post = 3
• col 2 tags = soccer basket tennis golf

Thanks

Answer 1

Use:

  SELECT p.id_post
         GROUP_CONCAT(DISTINCT t.name SEPARATOR ' ')
    FROM POST p
    JOIN POST_TAG pt ON pt.id_post = p.id_post
    JOIN TAG t ON t.id_tag = pt.id_post_tag
GROUP BY p.id_post

Be aware that the default separator is a comma, so you have to define a single space if you don't want that between the tag names.

Documentation:

• GROUP_CONCAT