Better query for joins

Question

Ok so I have three tables like so

Students

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

Class

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

Teacher

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

Ok so I want to find the teacher who is teaching both john and bob.

So far my query is,

 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;

So I wondering if this is the optimal way of constructing a query? I worry about this approach is that it doesn't seem to scale well as could be asking the common teacher of 25 students.

Thanks, Vinh

Answer 1

Use:

  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

The HAVING COUNT(DISTINCT s.name) must equal the number of names specified in the IN clause, because IN means the names could be John or Bob, in addition to teachers to have both students. The DISTINCT insulates from the possibility of duplicate names (2x John, etc), which would be a false positive.

Answer 2

Your query seems overly complex, just join the tables and filter on the results you want. Avoid unnecessary inline select statements. In this case I think group by may be more performant than distinct but prob not much in it. As long as the data is correctly indexed there shouldn't be scaling issues which such a simple query.

Select t.Name
From Teacher t
        inner join Class c on c.TeacherId = t.TeacherId
        inner join Students s1 on s1.StudentId=c.StudentId
        inner join Students s2 on s2.StudentId=c.StudentId 
Where s1.Name = 'Bob'
      and s2.Name = 'John'
Group By t.Name