PHP/MySQL:连接三个表并合并结果 [英] PHP / MySQL: Joining three tables and merging results

问题描述

可能重复:
我可以将多个MySQL行连接到一个字段中吗?

Possible Duplicate:
Can I concatenate multiple MySQL rows into one field?

我有三个表:

表#1:teacher

id
firstname
surname

表#2:course

id
name

表#3:courses_has_teachers

course_id
teacher_id

我想获得的是与老师信息相关的课程信息.我已经尝试过使用此查询:

What I want to get, is the course info with the teacher(s) info. I have tried it with this query:

SELECT * FROM 
teacher, course, courses_has_teachers
WHERE
courses_has_teachers.teacher_id = teacher.id
AND
course.id = courses_has_teachers.course.id

我得到了想要的东西，但是:如果一门课程有一位以上的老师，我想把结果结合起来.我不想只用一行包含相同的课程信息，而是想获得一个简单的包含课程信息和教师列表的行.

I get what I want, BUT: if a course has more than one teacher, I want to combine the results. Instead of multiple rows with same course info, I want to get one simple row with course info and a list of teachers.

不喜欢这样:

Name      | Teacher
--------------------
Course 1  | Person 1
Course 1  | Person 2

但是，这个:

Name      | Teacher
------------------------------
Course 1  | Person 1, Person 2

有人可以帮我吗?

推荐答案

使用GROUP_CONCAT.试试这个，

SELECT  a.name, GROUP_CONCAT(CONCAT(firstname, ' ', surname))
FROM    course a
        INNER JOIN courses_has_teachers b
            ON a.id = b.course_id
        INNER JOIN teacher c
            ON b.teacher_id = c.iD
GROUP BY a.name

这篇关于PHP/MySQL:连接三个表并合并结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！