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