有没有办法将关联查询显示为行？

Question

I have three tables, like this:

#Blog:
ID | Title
1    "My first blog"
2    "My second blog"

#Tags:
ID | Name
1   "Sport"
2   "Music

#tag_blog
Blog_id |   Tag_id
1           1
2           1
2           2

And I want to display them in my web page as

Blog_title  Tag_name
My first blog   Sport
My second blog  Sport, Music

in php file am doing somthing like

if ($query->num_rows() > 0)
{
   foreach ($query->result() as $row)
   {
      echo $row->title;
      echo $row->tag;
   }
}

But as I use a join, its display 3 lines with 1 tag by lines. Any idea to get the expected results?

Answer 1

Uou should use aggregate GROUP_CONCAT function -

SELECT b.Title AS Blog_title, GROUP_CONCAT(t.name) AS Tag_name FROM Blog b
  JOIN tag_blog tb
    ON tb.Blog_id = b.Blog_id
  JOIN Tags t
    ON t.Tag_id = tb.Tag_id
GROUP BY b.ID