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
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
Comment