mysql - 还有哪些老师正在教授 John Cullen.SQL 正在教授的类(class)

Question

我有多个表，但对于此查询只需要两个表，即 Faculty 和 CourseSection。我尝试了以下但失败了。如果有人可以提供帮助。

Select faculty.firstname,faculty.lastname,CourseSection.facid,courseID
from CourseSection,faculty
where CourseSection.facid in(
Select  CourseSection.CourseID 
from CourseSection
where CourseSection.facid =Faculty.facid and firstname='John' AND lastname='Cullen')

Answer 1

您应该在表之间使用左连接(假设您的表通过 CourseSection.facid = faculty.facid 相关)

  Select 
        faculty.firstname
        ,faculty.lastname
        , CourseSection.facid
        , CourseSection.courseID
  from CourseSection 
  LEFT JOIN  faculty on CourseSection.facid = faculty.facid
  where CourseSection.facid in (
  Select  CourseSection.CourseID 
      from CourseSection
      where CourseSection.facid =Faculty.facid 
      and firstname='John' AND lastname='Cullen')