mysql - sql语句复杂条件 "join"

Question

我有这 3 个表

1. course 
------------------------ 
CourseID     CourseName             
  1            English 1
  2            English 2
  3            Math 1
  4            Math 2
2. courseRequirements
------------------------
CourseID  ReqID                
   2        1
   4        3
3. studentcoursecomplete
-----------------------
CourseID   StudentID    
   1           5

我需要一个选择语句执行以下操作

1. 显示表格类(class)中的类(class)名称
2. 不显示学生完成的类(class)“studentcoursecomplete”
3. 不要显示有学生未完成要求的类(class)
4. 显示有要求学生完成的类(class)

在此示例中，结果将是

CourseID      CourseName             
  2            English 2
  3            Math 1

因为学生完成了英语1所以他看不到它
他没有完成数学 1 ..所以他看不到数学 2

我的选择语句执行 (1,2)
我该怎么做(3,4)
3-不显示有学生未完成要求的类(class)
4-显示有要求学生完成的类(class)

SELECT course.*
FROM course
LEFT JOIN studentcoursecomplete ON studentcoursecomplete.CourseID = course.CourseID
LEFT JOIN courseRequirements ON courseRequirements.CourseID = course.CourseID
WHERE studentcoursecomplete.CourseID IS NULL 

Answer 1

SELECT c.*, sc.* FROM semestercourses AS sc
NATURAL LEFT JOIN course AS c 
NATURAL LEFT JOIN courserequirements AS cr
NATURAL LEFT JOIN studentcoursecomplete AS scc  
WHERE 
sc.CourseID NOT IN (SELECT CourseID FROM studentcoursecomplete)
    AND 
(cr.RequirementID IS NULL OR cr.RequirementID IN (SELECT CourseID FROM studentcoursecomplete))