Get all tags of specific post in single record of mysql with XML PATH

Question

I have news table like this

news 
````````````
id          |   title     
------------+------------- 
1           |   test1
2           |   test2

and

tags
````````````
id          |   name     
------------+------------- 
1           |   tag1
2           |   tag2

and

news_tag
````````````
news_id     |   tag_id     
------------+------------- 
1           |   1
1           |   2
2           |   1

Now I want to get this

newsid     |   title      |   tag_name 
------------+-------------------------- 
1           |   test1     |    tag1,tag2
2           |   test2     |   tag1

Answer 1

you can show all the tag name on the same row using group_concat and group by

  select n.newsid, n.title, group_concat(name) tag_name
  from news n 
  inner join new_tag nt on nt.news_id = n.id 
  inner join tags t on t.id = nt.tag_id 
  group by n.newsid, n.title