mysql - SQL查询错误结果

Question

我有这个问题:

SELECT `completed`.`ID` AS `ID`,`completed`.`level` AS `level`,`completed`.`completed_in` AS `completed_in`, COUNT(1) AS `right_answers_num` 
FROM `completed` 
INNER JOIN `history` ON `history`.`ID` = `completed`.`ID` 
INNER JOIN `questions` ON `questions`.`ID` = `history`.`question`
WHERE `completed`.`student_id` = '1' AND `questions`.`answer` = `history`.`answer`       
GROUP BY `completed`.`ID` 
ORDER BY `completed`.`completed_in` DESC

我需要的是在已完成的表中获取每个测试的信息 (id,level,completed_in,right_answer_num)
该查询的问题是，如果没有一个正确的答案(history.answer = questions.answer)那么它不会返回行，而它应该返回行(id，level，completed_in)和 right_answer_num(counter ) 应该为零..

请帮助我，谢谢。

Answer 1

SELECT 
    completed.ID AS ID,
    completed.level AS level,
    completed.completed_in AS completed_in, 
    COUNT(questions.answer) AS right_answers_num 
FROM completed
        INNER JOIN history  ON history.ID = completed.ID
        LEFT JOIN questions ON questions.ID = history.question AND questions.answer = history.answer
WHERE 
    completed.student_id = '1'  
GROUP BY 
completed.ID 
ORDER BY completed.completed_in DESC