PHP / MySQL: Joining three tables and merging results

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?

You can use GROUP BY Name and the GROUP_CONCAT(Teacher) function. — Bud Damyanov, Sep 28 '12 at 07:44
you dont actually need that third table; you can include a foreign key to `teacher_id` in `course` — Amelia, Sep 28 '12 at 07:45
@Hiroto: of course he needs the 3rd table, since one course can have multiple teachers. — Flavius, Sep 28 '12 at 07:46
@OP: what you want to achieve is really unSQLish. What you've got and you consider wrong, is actually OK. — Flavius, Sep 28 '12 at 07:47
@Flavius you can put an index on the name in have multiple entries with different IDs and a non-unique course_id — Amelia, Sep 28 '12 at 07:49
@Hiroto will that still be normalized (up to 3rd NF at least)? — Flavius, Sep 28 '12 at 07:50

score 7 · Accepted Answer · answered Sep 28 '12 at 07:45

7

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

answered Sep 28 '12 at 07:45

John Woo

258,903
69
498
492

Mind explaining why this answer has been downvoted? – John Woo Sep 28 '12 at 07:59
Yeah, I was curious why our two answers just got slapped. – Fluffeh Sep 28 '12 at 08:00
@Fluffeh nevermind, some people just drop by and downvotes without leaving any comment, makes them feel happy. glad we could make them happy. haha :D – John Woo Sep 28 '12 at 08:01
@user1428033 `GROUP_CONCAT` is different from `CONCAT`. The first one is an aggregate function while the other is not. Anyway, you are welcome. feel free to ask question where you get confused :) – John Woo Sep 28 '12 at 08:06

score 3 · Answer 2 · edited Feb 15 '18 at 15:54

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.

Ashwini Agarwal · Answer 3 · 2018-02-20T05:01:35.077

2

Try using GROUP_CONCAT and GROUP BY.

edited Feb 20 '18 at 05:01

answered Sep 28 '12 at 07:43

Ashwini Agarwal

4,828
2
42
59

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Himanshu Sep 28 '12 at 07:51
I like answers to the official documentation. "the essential parts" is bullshit, if that link disappears, mysql would have disappeared altogether (or the explanations which you'd put in your answer would be outdated anyway). – Flavius Sep 28 '12 at 07:53

PHP / MySQL: Joining three tables and merging results

3 Answers3

Linked