Join a report table based on three relational tables

Question

Below are the given tables:

`student`                  `subject`
-------------------        ------------------- 
id    name                 id    subject
-------------------        -------------------
1     Alice                1     Maths
2     Bob                  2     Science
3     Eve                  3     Economics
-------------------        -------------------

`marks`
-----------------------------------------------------
id     student_id      subject_id       marks
-----------------------------------------------------
1      1               1                30
2      1               2                40
3      2               3                50
4      3               1                60
5      3               2                70
-----------------------------------------------------

I need an output which should look like below:

`o/p`
----------------------------------------------
name          subject          marks
----------------------------------------------
Alice         Maths            30
Alice         Science          10
Alice         Economics        NULL
Bob           Maths            NULL
Bob           Science          NULL
Bob           Economics        50
Eve           Maths            60
Eve           Science          70
Eve           Economics        NULL
----------------------------------------------

Please note that I am targeting MySQL 5.6.x syntax and I have created a SQL fiddle of above here to ease access to the question.

Answer 1

SELECT student.name,
       subject.subject,
       marks.marks 
    FROM student 
    JOIN subject ON student.id = subject.id
    JOIN marks ON student.id = marks.id 
    ORDER BY student.name,
             subject.subject

Answer 2

Here you go

select 
st.name,
su.subject,
m.marks
from student as st
cross join subject as su
left join marks as m on m.student_id=st.id and m.subject_id=su.id
order by st.name, su.subject