Search code examples
mysqldatabasejoinnormalization

How to JOIN tag_map in a normalized database

Consider an standard normalized many-to-many tag system (three tables of articles, tags, tag_map). I want to get a list of tags with associated articles; for example

Tag    Article_IDs
tag1   1,5,7
tag2   3,4,5,7,8
.....

How should I JOIN the tables to generate this list?

The naive way to simply count the number of rows in tag_map WHERE tag='something'. Badly, for this method, we need a separate query for every tag. For example, to generate a list for 20 tags, we need 20 queries (which is not rational). I hope to do this is one query with JOIN.

Solution

  • You can acomplish this with group_concat aggregation function. They are a lot of samples in stackoverflow.

    SELECT tag.id,
 GROUP_CONCAT(post.post_id)
 FROM 
  posts 
  inner join 
  post_tag on ...
  inner join 
  tags on ...
 GROUP BY tag.id;