7

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?

Improve this question
6
  • You can use GROUP BY Name and the GROUP_CONCAT(Teacher) function. Commented Sep 28, 2012 at 7:44
  • you dont actually need that third table; you can include a foreign key to teacher_id in course Commented Sep 28, 2012 at 7:45
  • @Hiroto: of course he needs the 3rd table, since one course can have multiple teachers. Commented Sep 28, 2012 at 7:46
  • @OP: what you want to achieve is really unSQLish. What you've got and you consider wrong, is actually OK. Commented Sep 28, 2012 at 7:47
  • @Flavius you can put an index on the name in have multiple entries with different IDs and a non-unique course_id Commented Sep 28, 2012 at 7:49

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Mind explaining why this answer has been downvoted?
Yeah, I was curious why our two answers just got slapped.
@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
@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 :)
3

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.

Improve this answer

Comments

2

Try using GROUP_CONCAT and GROUP BY.

Improve this answer

2 Comments

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.
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).

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.