Suggest Query In Other Way

Hi,

I have a table with two columns like this.

teacher table

teacher_id
1
2
3
4
5
6


student table

student_id --teacher_id

1 ----------- 10
1 ----------- 11
2 ----------- 12
2 ----------- 13
2 ----------- 14
3 ----------- 15
4 ----------- 16


Now i need the least assigned teacher_id's in the teachers table. i.e i need teacher_id's 5,6.

One possible way of writing the query is given below.



DECLARE @teachers TABLE
(
teacher_idint
)

INSERT INTO @teachers
SELECT1 UNION ALL
SELECT2 UNION ALL
SELECT3 UNION ALL
SELECT4 UNION ALL
SELECT5 UNION ALL
SELECT6

DECLARE @students TABLE
(
teacher_idint,
student_idint
)

INSERT INTO @students
SELECT1 , 10 UNION ALL
SELECT1 , 11 UNION ALL
SELECT2 , 12 UNION ALL
SELECT2 , 13 UNION ALL
SELECT2 , 14 UNION ALL
SELECT3 , 15 UNION ALL
SELECT4 , 16

-- query starts here

SELECT t.teacher_id
FROM @teachers t LEFT OUTER JOIN @students s
ON t.teacher_id = s.teacher_id
GROUP BY t.teacher_id
HAVING COUNT(s.teacher_id) =
(
SELECT TOP 1 COUNT(s.teacher_id)
FROM @teachers t LEFT OUTER JOIN @students s
ON t.teacher_id = s.teacher_id
GROUP BY t.teacher_id
ORDER BY COUNT(s.teacher_id)
)
-- query ends here


But the problem with this query is, i am using two outer joins on the same query and on the same tables....

If teacher table and student tables have few thousands of records, this query will not perform good....


Please suggest another way of writing the query which can perform well....

Thanks in advance

Suresh