尝试SQL来选择已注册和未注册模块的学生的某些属性

Question

转到链接查看表格表格链接：https://www.scribd.com/document/357739284/DBDI-Relational-Schema-Data

我想做的事情：尝试显示注册模块的学生姓名、电子邮件、模块ID及其结果，并显示同一列表中未注册模块ID 的学生

SQL我尝试了但问题：

SELECT sName, email, Module.moduleID, Module.results
FROM Student, Module, Registration
WHERE Student.studentID = Registration.studentID AND ModuleID.moduleID = Registration.moduleID
UNION 
SELECT sName, email, Module.moduleID, Module.results
FROM student
LEFT JOIN Registration ON Student.studentID = Registration. StudentID
WHERE Registration.moduleID IS NULL;

Answer 1

在最后一个左联接中，句点(.(和StudentID之间有一个空格。需要移除空间，如下所示。为了与底部查询保持一致，我还将上部查询更改为ANSI标准语法。在上面的查询中，Module表被误称为ModuleID。学生和模块之间的联接键不正确。已更正为StudentID。

SELECT sName, email, Module.moduleID, Registration.results
FROM Student    --<-- changed to ANSI Standard syntax
INNER JOIN Registration on Student.studentID = Registration.studentID
INNER JOIN Module on Registration.ModuleID = Module.ModuleID  --< Fixed Module table name
UNION 
SELECT sName, email, Registration.moduleID, Registration.results
FROM Student
LEFT JOIN Registration ON Student.StudentID = Registration.StudentID  
WHERE Registration.ModuleID IS NULL;