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
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.
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
精彩评论