MySQL查询显示基于三个表的格式

Question

I have three tables below that shows the student records, subjects and students with subjects.

I would like to ask what is the effective SQL query to show the results below. I can show it using JOIN but not with the format below.

+------+-----------+-----------+-----+----------+-----------+--------+
| Name | Address   | Telephone | Sex | Subjects | Teacher   | Active |
+------+-----------+-----------+-----+----------+-----------+--------+
| John | somewhere | 12345     | M   |          | Teacher 1 | YES    |
| John | somewhere | 12345     | M   | Math     |           | YES    |
| John | somewhere | 12345     | M   | Science  |           | YES    |
| John | somewhere | 12345     | M   | English  |           | YES    |
| Matt | somewhere | 123456    | M   |          | Teacher 2 | YES    |
| Matt | somewhere | 23456     | M   | Math     |           | YES    |
| Matt | somewhere | 123456    | M   | Science  |           | YES    |
| Girl | somewhere | 5431      | F   |          | Teacher3  | YES    |
| Girl | somewhere | 5431      | F   | Physics  |           | YES    |
| Girl | somewhere | 5431      | F   | Math     |           | YES    |
+------+-----------+-----------+-----+----------+-----------+--------+

select * from student_record;
+------------+------+-----------------+-----------+-----+----------+--------+
| id_student | name | address         | telephone | sex | teacher  | active |
+------------+------+-----------------+-----------+-----+----------+--------+
|          1 | John | Somewhere       | 12345     | M   | Teacher  | 0      |
|          2 | Matt | Somewhere There | 12345222  | M   | Teacher1 | 0      |
|          3 | Girl | Somewhere here  | 3333      | F   | Teacher2 | 0      |
+------------+------+-----------------+-----------+-----+----------+--------+

select * from subjects;
+------------+--------------+---------------------+
| id_subject | subject_name | subject_description |
+------------+--------------+---------------------+
|          1 | Math         | Math                |
|          2 | Science      | Science             |
|          3 | English      | English             |
|          4 | Physics      | Physics             |
+------------+--------------+---------------------+

select * from with_subjects;
+--------------------+--------------------+------------+
| id_student_subject | student_id_subject | student_id |
+--------------------+--------------------+------------+
|                  1 |                  1 |          1 |
|                  2 |                  2 |          1 |
|                  3 |                  3 |          1 |
|                  4 |                  4 |          1 |
|                  5 |                  4 |          2 |
|                  6 |                  3 |          2 |
|                  8 |                  1 |          2 |
|                  9 |                  1 |          3 |
|                 10 |                  2 |          3 |
|                 11 |                  3 |          3 |
|                 12 |                  4 |          3 |
+--------------------+--------------------+------------+

Answer 1

how about

select a.name as "Name",a.address as "Address",a.telephone as "Telephone" ,a.sex as "Sex",null as "Subject",a.teacher as "Teacher",a.active as "Active" from student_record as a union a.name as "Name",a.address as "Address",a.telephone as "Telephone" ,a.sex as "Sex",b.subject_name as "Subject",null as "Teacher",a.active as "Active" from (student_record as a inner join with_subjects as c on a.id_student = c.student_id) inner join subjects as b on c.student_id_subject = b.id_subject

Not tested it. It wotn be in the same order as your example, but should have all of the data there