mysql - 连接 3 个表，包括空值 Mysql

Question

我们在 MySQL 中有三个表。我们的查询必须加入:

1) student 
2) subject
3) mark

学生

id name class year  gender address
1 Ganesh Wagh SSC 2010 male Bhandup, Maharashtra
2 Shailesh Penkar HSC 2010 male Ghatkoper, Mumbai
3 Pooja Palshetkar BE 2014 female Vichumbe, Panvel

主题

id name
1 English
2 Hindi
3 Maths

标记

id studentid subjectid mark
1 1 1 70
2 1 2 80
3 1 3 90
4 2 1 60
5 2 2 50
6 2 3 60

和我想要的输出

Name        subject mark
Ganesh Wagh English 70
Ganesh Wagh Hindi 80
Ganesh Wagh Maths 90
Shailesh Penkar English 60
Shailesh Penkar Hindi 50
Shailesh Penkar Maths 60
Pooja Palshetkar English 0
Pooja Palshetkar Hindi 0
Pooja Palshetkar Maths 0

我试过了，但没用:

SELECT 
  student.name, 
  subject.name, 
  mark.mark 
FROM 
(mark AS mark 
RIGHT JOIN student AS student 
  ON student.id = mark.studentid) 
LEFT JOIN subject AS subject 
  ON mark.subjectid = subject.id 
ORDER BY student.id ASC

Answer 1

您可能想要进行交叉连接，以便获得所有科目和所有学生，而不管 mark 中是否有值。有点像

select ...
from (student st cross join subject su)
      left join mark m on st.id = mark.studentid and su.id = mark.subjectid
order by student id asc