您需要查看属于给定学生的所有行，因此 where 子句不能做你想做的事。相反，您可以将聚合和文件管理器与 having 条款：

select s.studentname 
from student s
inner join attendance a on s.studentId = a.studid
inner join lecture l on l.lecId = a.lectureid 
group by s.studentId, s.studentname 
having max(l.professor= 'JOHN') = 1 and max(l.professor = 'JOSEPH') = 0

如果你想让老师们把约翰的课都加上，而不是约瑟夫的课，那么：

select s.studentname 
from student s
inner join attendance a on s.studentId = a.studid
inner join lecture l on l.lecId = a.lectureid 
group by s.studentId, s.studentname 
having 
    sum(l.professor= 'JOHN') = (select count(*) from lecture where professor = 'JOHN')
    and max(l.professor = 'JOSEPH') = 0

如果您的mysql版本支持组\u concat，您可以尝试下面的脚本-
此处演示

SELECT S.StudentId,S.StudentName, GROUP_CONCAT(DISTINCT L.Professor)
FROM Attendance A
INNER JOIN Lecture L ON A.LectureId = L.LecId
INNER JOIN Student S ON A.StudId = S.StudentId
GROUP BY S.StudentId,S.StudentName
HAVING  GROUP_CONCAT(DISTINCT L.Professor) = 'JOHN'

上面的查询将返回仅从“john”学习课程的学生列表。没有其他人。
但如果您的要求更改为在列表中添加更多教授，则可以在having子句检查中相应地添加教授姓名。但你需要在这个案子上申请一个合适的排序。

尝试：

select * from Student st
where exists(select * from Lecture l
             join Attendance a
             on l.LecId = a.LectureId
             where l.Professor = 'John'
             and a.StudId = st.StudentId)
and exists(select * from Lecture l
               join Attendance a
               on l.LecId = a.LectureId
               where l.Professor = 'Joseph'
               and a.StudId = st.StudentId)

select s.StudentName 
from Student s
inner join Attendance a on s.StudentId = a.StudId
inner join Lecture l on l.LecId = a.LectureId 
group by s.StudentId, s.StudentName 
having 
    sum(l.Professor= 'JOHN') = (select count(*) from lecture where Professor = 'JOHN')
    and max(l.Professor = 'JOSEPH') = 0
order by s.StudentName

采访中提问（makemytrip）

我重新措辞了你的加入。。。但似乎奏效了。
此查询是否返回您要查找的数据集？

SELECT s.studentname, l.Subject, l.Professor
FROM Student s JOIN Attendance a
ON s.StudentId = a.StudId
JOIN Lecture l 
ON l.lecId = a.lectureid
WHERE l.professor='JOHN' 
  AND l.professor != 'JOSEPH';