Omega P Omega P - 3 months ago 7
SQL Question

SQL query select from 3 table Many-to-Many

I have 3 tables:

table

post
:

   post_id    |    title
---------------------------------
1 | post number 1
2 | post number 2
3 | post number 3


table
categories
:

   category_id   |   cate_name
--------------------------------
1 | video
2 | review
3 | gameplay


table
post_categories
:

 post_id   |   category_id
---------------------------------
1 | 1
2 | 2
3 | 3
2 | 1
3 | 2
1 | 3


How can do I select
post's title
and
category's name
? Result should look like this:

        title       |   cate_name
-----------------------------------------
post number 1 | video, gameplay
post number 2 | video, review
post number 3 | review, gameplay


Is this possible?

Answer

Ok try this.

SELECT DISTINCT
  post.title as title,
  GROUP_CONCAT(categories.cate_name) as name
  FROM post_categories
    INNER JOIN post
        ON post_categories.post_id = post.post_id
    INNER JOIN categories
        ON post_categories.category_id = categories.category_id
  GROUP BY post.post_id;