Mysql查询多个内部连接和左连接

Question

这是我的数据库模式

学生桌：

studentid (PK)   |studentname 

1                |A  

类表：

classid (PK)     |classname    

1                |Math
2                |Science

教学表：

teachid  (PK)    |  classid   |   studentid 

1                |  1         |   1
2                |  2         |   1

问答表：

quizid  (PK)     |classid     | quizname

1                |1           | mathquiz
2                |2           | science quiz 

问答表：

quizscoreid(PK)  | quizid     |studentid   |score
1                | 1          |1           |60
2                | 2          |1           |0

这是我使用的sql查询：

SELECT
    quiz.quizid,
    teach.classid,
    student.studentname,
    student.studentid,
    quizscore.score,
    quiz.quizname,
    class.classname
FROM teach 
INNER JOIN class
    ON teach.classid = class.classid
INNER JOIN student
    ON teach.studentid = student.studentid
INNER JOIN quiz
    ON quiz.classid = '1'
LEFT JOIN quizscore
    ON teach.studentid = quizscore.studentid
WHERE
    teach.classid='1' AND
    teach.classid = quiz.classid AND
    quiz.quizid = '1'
ORDER BY studentid ASC

它的输出如下：

studentid   |quizid    |classid    | studentname  |classname   | quizname   | score
1           |    1     |1          |  A           |Math        | mathquiz   | 60
1           |    1     |1          |  A           |Science     | mathquiz   | 0

当我期待这样的时候：

studentid   | quizid     |classid    | studentname  |classname   | quizname   | score
1           | 1          |1          |  A           |Math        | mathquiz   | 60

请帮助我更正我的sql查询。

Answer 1

我发现有两个联接缺失了，我在SQL中做了更正。试一试，希望你能得到预期的结果。

SELECT
    quiz.quizid,
    teach.classid,
    student.studentname,
    student.studentid,
    quizscore.score,
    quiz.quizname,
    class.classname
FROM teach 
INNER JOIN class
    ON teach.classid = class.classid
INNER JOIN student
    ON teach.studentid = student.studentid
INNER JOIN quiz
    ON quiz.classid = tech.classid
LEFT JOIN quizscore
    ON (teach.studentid = quizscore.studentid
    and quiz.quizid = quizscore.quizid)
WHERE
    teach.classid='1' AND
    teach.classid = quiz.classid AND
    quiz.quizid = '1' and
    quiz.classid = '1'
ORDER BY studentid ASC

Answer 2

SELECT 
A.studentid, 
D.quizid, 
A.classid, 
C.studentname, 
B.classname, 
D.quizname, 
E.score
FROM
teach A 
LEFT JOIN class B ON A.classid=B.classid
LEFT JOIN student C ON A.studentid=C.studentid
LEFT JOIN quiz D ON A.classid=D.classid
LEFT JOIN quizscore E ON D.quizid=E.quizid AND E.studentid=A.studentid
WHERE 
A.studentid=1 AND 
D.quizid=1 AND  
A.classid=1;

演示