sql - 更好的连接查询

Question

好吧，我有三个这样的表

学生

Name        StudendId
----------------------
John            1
Jane            2
Bob             3
Betty           4

类

StudentId   TeacherId
----------------------
1            1
2            2
3            1
4            2
1            3
4            3

老师

Name          TeacherId
------------------------
Jim               1
Joan              2
Jill              3

好的，所以我想找到同时教约翰和鲍勃的老师。

到目前为止我的查询是，

 select distinct Teachers.Name from Teachers, 
   ((select Teachers.Name from Class, Students 
     where Students.StudendId = Class.StudentId and Students.Name = 'John') as tbl1
    join
   (select Class.TeacherId from Class, Students 
     where Students.StudendId = Class.StudentId and Students.Name = 'Bob') as tbl2
   on tbl1.TeacherId = tbl2.TeacherId) where Teachers.TeacherId = tbl1.TeacherId;

所以我想知道这是否是构建查询的最佳方式？我担心这种方法似乎无法像询问 25 名学生的共同老师那样具有很好的扩展性。

谢谢，荣市

Answer 1

用途:

  SELECT t.name
    FROM TEACHERS t
    JOIN CLASS c ON c.teacherid = t.teacherid
    JOIN STUDENTS s ON s.studentid = c.studentid
                   AND s.name IN ('John', 'Bob')
GROUP BY t.name
  HAVING COUNT(DISTINCT s.name) = 2

HAVING COUNT(DISTINCT s.name) 必须等于 IN 子句中指定的姓名数量，因为 IN 表示姓名可以是 John 或 Bob，此外教师还可以有两个学生。 DISTINCT 避免了重复名称的可能性(2x John 等)，这将是误报。