Displaying MySQL results from a relation of a relation

Question

If I have three tables, and I'm trying to loop through all posts:

posts: post_id, post_title, post_content
tag_relations: post_id, tag_id
tags: tag_ig, tag_name

If each post will have multiple tags, how would I display each tag for each post?

As in, how would I have to setup my SQL query, and how would I print the results? Would it be easier just to use multiple queries?

Answer 1

MySQL provides the useful GROUP_CONCAT() function, which you may want to use as follows:

SELECT    p.post_id, p.post_title, GROUP_CONCAT(tag_name SEPARATOR ', ') tags
FROM      posts p
JOIN      tag_relations tr ON (tr.post_id = p.post_id)
JOIN      tags t ON (t.tag_id = tr.tag_id)
GROUP BY  p.post_id;

Test case:

CREATE TABLE posts (post_id int, post_title varchar(50));
CREATE TABLE tags (tag_id int, tag_name varchar(50));
CREATE TABLE tag_relations (post_id int, tag_id int);

INSERT INTO posts VALUES (1, 'post 1');
INSERT INTO posts VALUES (2, 'post 2');
INSERT INTO posts VALUES (3, 'post 3');

INSERT INTO tags VALUES (1, 'mysql');
INSERT INTO tags VALUES (2, 'sql');
INSERT INTO tags VALUES (3, 'javascript');
INSERT INTO tags VALUES (4, 'python');

INSERT INTO tag_relations VALUES (1, 1);
INSERT INTO tag_relations VALUES (1, 2);
INSERT INTO tag_relations VALUES (2, 3);
INSERT INTO tag_relations VALUES (3, 2);
INSERT INTO tag_relations VALUES (3, 4);

Result:

+---------+------------+-------------+
| post_id | post_title | tags        |
+---------+------------+-------------+
|       1 | post 1     | mysql, sql  |
|       2 | post 2     | javascript  |
|       3 | post 3     | sql, python |
+---------+------------+-------------+
3 rows in set (0.00 sec)

As @Alfonso de la Osa noted in a comment below, you can use left outer joins instead of inner joins to get back posts without any tags.