Join with another table containing tags

Question

I have a table called "posts" with the following struture:

post_id  |  post_body  | post_posterid

And I have another table called "tags":

tag_id  |  tag_name

A third table stores the relation between the two tables (will have multiple tags_id for each post_id as separate records):

post_id  |  tag_id

This is similar to stackoverflow's tagging that i am trying to create.

But I do not know how to join the tables and to concatenate tags for each post, in a single record. Like this:

post_id  |  post_body  |  post_postername  |  tag_id1  |  tag_id2  |  tag_id3  ..... tag_name1  |  tag_name2  | tag_name3 ....

So while fetching from mysql using PDO, I can avoid further queries to get tag names.

EDIT More details:

First table("posts") contains post id(a unique id), posted message and poster's id(unique poster id which is stored in table "poster"). Second table "tags" contains possible tags(like "php", "vb.net", etc. like in stackoverflow) with unique tag_id. Third table contains the relation between posted message and tags. So it contains post_id and its corresponding tag_id. It is possible to have more than 1 tag assigned to same post.

What i want: when queried, i can join "posts" with "posters" table to get name. But i want to get tags for each posts also.

GROUP_CONCAT() is answer for my question. But how to obtain it as tag_id - tag_name pair, instead of all tag_id in a field and all tag_name in another.

Eg: 1-VB , 5-PHP , 19-SQL

I will separate this pairs in PHP

What i am trying is same as stackoverflow's tagging system.

Answer 1

keyword is GROUP_CONCAT

and query is like

SELECT 
posts.*,
GROUP_CONCAT(tags.tag_name) tags
FROM
posts
LEFT JOIN relation ON relation.post_id  = posts.id
LEFT JOIN tags ON tags.tag_id = relation.tag_id
GROUP BY posts.id

hope this help