PHP / MySQL: Joining three tables and merging results [duplicate]

Question

Possible Duplicate:
Can I concatenate 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 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.

NOT like this:

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

BUT this:

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

Could someone help me with this?

Answer 1

Use GROUP_CONCAT. try this one,

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

You want to use a mysql function called group_concat to achieve this. Your query will look something similar to 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 have re-written this query in ANSI-92 format, which you might not be familiar with, but is can make queries with multiple joins SO 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

Additionally, you might want to have a read of a lengthy Q&A that I wrote which you might find useful.

Answer 3

Try using GROUP_CONCAT and GROUP BY.