Search code examples
mysqlrdbmsnode-mysql

Getting all results in Join in MySQL on reduced tables

My 3 tables:

posts

enter image description here

tags:

enter image description here

post2tags:

enter image description here

So in the posts table I did not reference anything related to tags. And in the tags table, no reference for posts. I created a new table, posts2tags and referenced the posts for tags there.

Question: Is this the proper way to do it? Do I need to have any reference for posts in tags table and vice versa?

What I want to achieve is to return everything from the posts table and all tag names where the post is referenced for tags (in posts2tags table) and have all the tag names in an array/single row in the result. Like this:

enter image description here

Tried : 

SELECT p.idPost, p.title, t.name from posts as p, tags as t, post2tags
 WHERE t.idTags=post2tags.idTags

Result:

enter image description here

I'm learning to join multiple tables and reduce the table to only required information from it. So looking for advice on that too.

Solution

  • Yes its the right way to store ManyToMany relation the junction table (post2tags) will hold the references for posts and tags to relate them. To get the expected results you can use left join to get all posts whether they have tags or not

    select p.idPost, p.title, t.name
from posts as p
left join post2tags pt on (p.idPost = pt.idPost)
left join tags t  on (t.idTags = pt.idTags)
order by p.idPost

    this will return you all the post with their related tags, Each will be returned multiple times as no. of assigned tags like 

    idPost  title  name
-------------------
 1      test   tag1
 1      test   tag2
 2      test   tag1
 2      test   tag2
 3      test   null

    then in application code you can display these results as per you desired format by doing some if/else logic to show post once only and display its tags.

    Another hacky way would be use of group_concat function which returns comma separated list of values per group like below but it has some limitation of character length.

    select p.idPost, p.title, group_concat(t.name) as `name`
from posts as p
left join post2tags pt on (p.idPost = pt.idPost)
left join tags t  on (t.idTags = pt.idTags)
group by p.idPost
order by p.idPost

    this will give you results as per your desired view

    idPost  title  name
-------------------
 1      test   tag1,tag2
 2      test   tag1,tag2
 3      test   null