查找“缺失”行

Question

我有一个考试数据库，有4个表格：用户，答案，问题和主题。

user table 
U_id | name 

subjects table 
S_id | Subject 

questions table 
Q_id | S_id | question | Correct 

answers table 
U_id | Q_id | answers 

我需要找到每个用户的每个主题的正确答案的数量。

我的查询

select U_id,questions.S_id ,count(Q_id) 
from answers inner join questions on questions.q_id = answers.q_id 
where questions.Correct = answers.answer 
group by answers.U_id,questions.S_id 

result: 
1 | s1 | 2 
1 | s2 | 3 
1 | s3 | 1 
2 | s1 | 1 
2 | s2 | 1 

，让我每科的正确答案的数目，但如果用户没有正确答案的S_ID不显示，我需要显示那些0

我需要的结果是

result: 
1 | s1 | 2 
1 | s2 | 3 
1 | s3 | 1 
2 | s1 | 1 
2 | s2 | 1 
2 | s3 | 0 

任何帮助吗？

请注意我使用MySQL，但MS SQL server anwers很好。

Answer 1

select u.U_id, q.S_id, count(a.q_id) 
from users u 
cross join questions q 
left join answers a on q.q_id = a.q_id and u.u_id = a.u_id and q.Correct = a.answer 
group by u.U_id, q.S_id