combine multiple row results in one comma sep. field using join

[veliscorin]

Hi guys,

Currently I have student's management database project going on. For a page which "list all students" I would wanna do a

SELECT * FROM tbl_students

However each student has 1 or more subjects he/she can take. So I would like to also display the subjects in the "list all students" page, in a comma-delimited form
ie: "Maths, English, Science"

In the case of joining the tables, i can simply join based on the student_id field, but that will give me more than 10 records if i had 10 students in the database. How will I go about joining and grouping them so that i have something like this:

id     name        subjects
1      John         English, Maths
2      Doe          Science, English

Many Thanks!

[ronverdonk]

From you post I cannot make up if there is one table or if there are 2 tables involved. Please show the involved tables with their relevant columns.

Ronald

[veliscorin]

assuming it is not in any normalized form

Students Table {student_id, student_name etc..}
Subjects Table {subject_id, student_id, subject_name etc..}

[ronverdonk]

Not normalized at all! Have a try with this one[code=sql]select student_name as Student_name, GROUP_CONCAT(su bject_name) as Courses from students as s1 left join subjects as s2 on s1.student_id=s 2.student_id group by student_name;[/code]Ronald

[veliscorin]

wow thanks ronald. works like a charm..
i never knew there was this function in mysql ^^

Regards

[ronverdonk]

There are sooo many functions in MySQL, you'd be amazed. Anyway, glad I could help out. See you next time.

Ronald