sql - How to build query? Any idea? -

3 tables:

mark table:

student_id sa_id marks 1           1    75 1           2    80 1           3    100 2           4    85 2           5    90 2           6    60 

course table:

course_code    sat_id    name_code aaa 100          1        midterm1 aaa 100          2        midterm2 aaa 100          3        final bbb 200          4        midterm1 bbb 200          5        midterm2 bbb 200          6        final    

transform table:

sa_id     sat_id 1            1 2            2 3            3 4            4 5            5 6            6     

select course.course_code, mark.marks  mark     left outer join transform on transform.sa_id = mark.sa_id   left outer join course on course.sat_id = transfrom.sat_id course.name_code = 'midterm1' 

at above query midterm1 result, can extract mid2 , final

select mark.student_id,course.course_code, mark.marks, course.name_code  mark    left outer join transform on transform.sa_id = mark.sa_id   left outer join course on course.sat_id = transfrom.sat_id order mark.student_id, course.course_code 

result give:

student_id course_code  marks name_code 1           aaa 100      75     midterm1  1           aaa 100      80     midterm2 1           aaa 100      100    final 2           bbb 200      85     midterm1 2           bbb 200      90     midterm2  2           bbb 200      60     final 

so how build query should be

student_id   course_code   midterm1   midterm2  final 1             aaa 100       75          80       100    2             bbb 200       85          90       60 

you can use case when , group (and fake aggregation function)

select        student_id     , course_code     , max(case when name_code ='midterm1' mark else null) midterm1     , max(case when name_code ='midterm2' mark else null) midterm2         , max(case when name_code ='final' mark else null) final mark left outer join transform on transform.sa_id = mark.sa_id left outer join course on course.sat_id = transfrom.sat_id group  student_id, course_code order mark.student_id, course.course_code 

but if don't aggregation functio can use 3 seleft join on mark

  select          mm1.student_id       , mm1.course_code       , mm1.mark  midterm1       , mm2.mark  midterm2       , mm3.mark  finale       mark  mm1    left join mark mm2 on mm1.student_id = mm2.student_id , mm1.course_code = mm2.course_code   left join mark mm3 on mm1.student_id = mm3.student_id , mm1.course_code = mm3.course_code    left outer join transform on transform.sa_id = mm1.sa_id   left outer join course on course.sat_id = transfrom.sat_id   mm1.name_code = 'midterm1'   , mm2.name_code = 'midterm2'   , mm3.name_code = 'final'