PHP SQL left join fetch all

Question

我有 3 个 mysql 表，我试图从中获取数据表:列表

list_id  |  name  |  description
-------------------------------------
1234     |  name1 | sample description1
1235     |  name2 | sample description2

表:list_to_category

id     |  list_id  | category_id
--------------------------------
1      |  1234     | 1
2      |  1234     | 2
3      |  1234     | 3
4      |  1235     | 2
5      |  1235     | 3

和表:类别

id     |  title      | parent_id
--------------------------------
1      |  Category 1 | 0
2      |  Category 2 | 0
3      |  Category 3 | 0

我想从 PHP SQL 查询中获取如下数据

1. name1 - category 1, category 2, category 3
2. name2 - category 2, category 3

我试过下面的查询

SELECT list.name, category.title FROM list

        LEFT JOIN list_to_category
        ON list.id = list_to_category.list_id

        LEFT JOIN category
        ON list_to_category.id = category.id

这只给我分配给这样的列表的单个类别名称

1. name1 - category 1
2. name2 - category 2

是否可以在单个查询中？

Answer 1

您可以为此使用 GROUP_CONCAT:

select
    l.list_id,
    l.name,
    group_concat(distinct c.title) categories
from list l
left join list_to_category lc
on l.list_id = lc.list_id
left join category c
on lc.category_id = c.id
group by l.list_id