php - SQL查询从3个表中选择多对多

Question

我有 3 张 table :

表帖子:

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

表类别:

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

表post_categories:

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

如何选择帖子标题和类别名称？结果应如下所示:

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

这可能吗？

Answer 1

好的，试试这个。

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