Avoid duplicated rows in SQL query when JOINing many to many tables

Question

I have a basic blog system with tables for posts, authors and tags.

One author can write a post but a post can only be written by an author (one to many relationship). One tag can appear in many different posts and any post can have several tags (many to many relationship). In that case I've created a 4th table to link posts and tags as follows:

 post_id -> posts_tag
|    1     |    1    |
|    1     |    2    |
|    2     |    2    |
|    4     |    1    |

I need a single query to be able to list every post along with its user and its tags (if any). I'm pretty close with a double JOIN query but I get duplicated rows for posts with more than one tag (everything in that rows is duplicated but the tag register). The query I'm using goes as follows:

SELECT title,
     table_users.username author,
     table_tags.tagname tag
  FROM table_posts
  JOIN table_users 
    ON table_posts.user_id = table_users.id
  LEFT 
  JOIN table_posts_tags 
    ON table_posts.id = table_posts_tags.post_id
  LEFT 
  JOIN table_tags 
    ON table_tags.id = table_posts_tags.tag_id

Could any one suggest an amend to this query or a new proper one to solve the row duplication issue* when there's more than one tag associated to the same post? Ty

(*) To make clear: in the above table the query will throw 4 rows when it should be throwing 3, 1 for post #1 (with 2 tags), one for post #2 and one for post #4.

Table Recreate

CREATE TABLE `table_posts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(120) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`),
)

CREATE TABLE `table_tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name_tag` varchar(18) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `table_posts_tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `post_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tag_id` (`tag_id`),
  KEY `FK_t_posts_tags_t_posts` (`post_id`),
  CONSTRAINT `FK_t_posts_tags_t_posts` FOREIGN KEY (`post_id`) REFERENCES `t_posts` (`id`),
  CONSTRAINT `FK_t_posts_tags_t_tags` FOREIGN KEY (`tag_id`) REFERENCES `t_tags` (`id`)
) 

CREATE TABLE `table_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `banned` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_t_users_t_roles` (`role_id`),
  CONSTRAINT `FK_t_users_t_roles` FOREIGN KEY (`role_id`) REFERENCES `t_roles` (`id`)
)

user_10676716 · Answer

One option aggregates the tags in a CSV list using group by and group_concat():

select p.title, u.username author, group_concat(t.tagname) tagnames
from table_posts p
inner join table_users u       on u.id = p.user_id
left join  table_posts_tags pt on pt.post_id = p.id
left join  table_tags t        on t.id = tp.tag_id
group by p.id, p.title, u.username

Note that I added table aliases to the query, and used them to qualify all columns; this makes the query shorter and easier to write and read.

Avoid duplicated rows in SQL query when JOINing many to many tables

1 Answers