mysql: get all rows into 1 column

Question

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

OMG Ponies · Accepted Answer · 2010-05-28 18:30:07Z

2

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

answered May 28, 2010 at 18:30

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andres SK Over a year ago

worked great! just one more doubt... how do i limit the amount of tags, for example: 3 -- i tried to use a LIMIT approach but it won't work.

OMG Ponies Over a year ago

@andufo: You have to use a variable to provide analytical functionality because it doesn't natively exist in MySQL.

Collectives™ on Stack Overflow

mysql: get all rows into 1 column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related