Can I reduce the lines of my MySQL output by concatenation?

[Asteras]

Hi All!!!

Let us consider two tables in a MySQL database:

1) C_T is a table of school classes and the teachers that teach in
them:

select * from C_T;

+-------+---------+
| Class | Teacher |
+-------+---------+
| A | John |
| A | Robert |
| B | Julian |
| C | George |
| C | John |
+-------+---------+

2) T_S is a table that shows the subject of each teacher.

select * from T_S;

+---------+-----------+
| Teacher | Subject |
+---------+-----------+
| John | Maths |
| Julian | Chemistry |
| George | Physics |
| Robert | Music |
+---------+-----------+

This is the ERD schema of the MySQL database:

+-----------------+ +-------------+
| C_T | | T_S |
+-----------------+\ +-------------+
| Class PK |-+---+| Teacher PK |
| Teacher PK, FK |/ | Subject |
+-----------------+ +-------------+

As you can see, the tables have an "one to many" relationship.

C_T has a composite primary key (Class and Teacher) and T_S's primary
key is Teacher (which is exported as an identifying foreign key in C_T
table).

If I try:

select C_T.Class, C_T.Teacher, T_S.Subject
from C_T, T_S
where C_T.Teacher=T_S .Teacher;

+-------+---------+-----------+
| Class | Teacher | Subject |
+-------+---------+-----------+
| A | John | Maths |
| A | Robert | Music |
| B | Julian | Chemistry |
| C | George | Physics |
| C | John | Maths |
+-------+---------+-----------+

I can see all the entries of C_T (attributes Class and Teacher)
together with the relevent entries of T_S from attribute Subject.

Is there a way I can get in a SINGLE line ALL the entries of a single
primary key of the first table? In my example, can I get a 3-line
output where each line would contain each Class, the names of the
Teachers separated by a semicolon, and the Subjects taught in the
Class, also separated by a semicolon? In other words, I want to get
the following:

+-------+-------------+---------------+
| Class | Teacher | Subject |
+-------+-------------+---------------+
| A | John;Robert | Maths,Music |
| B | Julian | Chemistry |
| C | George;John | Physics;Maths |
+-------+-------------+---------------+

If there is a way, I would be indebted if you could show it to me.

Regards,

Asteras

[Bill Karwin]

Re: Can I reduce the lines of my MySQL output by concatenation?

Asteras wrote:[color=blue]
> Is there a way I can get in a SINGLE line ALL the entries of a single
> primary key of the first table?[/color]

This is a very similar problem to another that just came across this
newsgroup today, under the subject "is this possible with selects and
joins?"

My answer would be very similar: fetch the data in its "expanded" form,
and then do the concatenation in application code.

Regards,
Bill K.