PHP / MySQL: joining three tables and joining results

Question

Possible duplicate:
Is it possible to combine multiple MySQL rows into one field?

I have three tables:

Table # 1: teacher

 id firstname surname 

Table # 2: course

 id name 

Table 3: courses_has_teachers

 course_id teacher_id 

What I want to get is course information with information about teachers. I tried this 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 the course has more than one teacher, I want to combine the results. Instead of several lines with the same course information, I want to get one simple line with course information and a list of teachers.

I do not like:

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

But this:

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

Can anyone help me with this?

Answer 1

Use GROUP_CONCAT . try it,

 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 

Answer 2

Try using GROUP_CONCAT and GROUP BY .

Answer 3

To achieve this, you want to use a mysql function called group_concat. Your query will look something like this:

 SELECT courseName, group_concat(teacherName) FROM teacher, course, courses_has_teachers WHERE courses_has_teachers.teacher_id = teacher.id AND course.id = courses_has_teachers.course.id GROUP BY courseName 

I rewrote this query in ANSI-92 format, which you may not be familiar with, but can make queries with multiple connections. SO is much easier to read:

 SELECT courseName, group_concat(teacherName) FROM teacher join course on courses_has_teachers.teacher_id = teacher.id join courses_has_teachers on course.id = courses_has_teachers.course.id WHERE // Any conditions you want on the query GROUP BY courseName 

Also, you might want to read the long Q&A that I wrote, which may come in handy.