December 3, 2007 at 10:42 pm
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_id int
)
INSERT INTO @teachers
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
DECLARE @students TABLE
(
teacher_id int,
student_id int
)
INSERT INTO @students
SELECT 1 , 10 UNION ALL
SELECT 1 , 11 UNION ALL
SELECT 2 , 12 UNION ALL
SELECT 2 , 13 UNION ALL
SELECT 2 , 14 UNION ALL
SELECT 3 , 15 UNION ALL
SELECT 4 , 16
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)
)
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
December 3, 2007 at 11:00 pm
Is this what you're looking for?
SELECTTOP 1 WITH TIES t.teacher_id, COUNT( s.teacher_id ) AS NoOfStudents
FROM@teachers t
LEFT JOIN @students s on t.teacher_id = s.teacher_id
GROUP BY t.teacher_id
ORDER BY NoOfStudents
--Ramesh
December 3, 2007 at 11:28 pm
Actually i need only teacher id/id's (but not the count) with minimum students assigned to them.
December 4, 2007 at 12:12 am
Then just remove it from the select list & add the aggregate to order by clause..
SELECTTOP 1 WITH TIES t.teacher_id
FROM@teachers t
LEFT JOIN @students s on t.teacher_id = s.teacher_id
GROUP BY t.teacher_id
ORDER BY COUNT( s.teacher_id )
--Ramesh
December 4, 2007 at 12:20 am
Hi,
One solution to the problem can be :
select teachid,stds from(
select t.teacherid as teachID,count(s.teacherid) as Stds from teacher t
left outer join student s on t.teacherid = s.teacherid
group by t.teacherid) as avi where stds =
(select min(stds)from (select t.teacherid as teachID,count(s.teacherid) as Stds from teacher t
left outer join student s on t.teacherid = s.teacherid
group by t.teacherid) as avi)
regards,
Avaneesh Bajoria.
December 4, 2007 at 2:06 am
You are using two joins in the same query......
But i want to avoid that.... as the table size is big....
December 4, 2007 at 2:10 am
Hey, haven't you seen my above post?
--Ramesh
December 4, 2007 at 2:30 am
use the below querry,
select top 1 with ties teacher_id from
(select t.teacher_id, count(*) 'cnt' from teacher t
left join student s on t.teacher_id = s.teacher_id
group by t.teacher_id)
order by cnt
Giri
December 4, 2007 at 3:15 am
Hi ramesh,
Thanks a lot.....
That query worked fine.....
It really helped me alot
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply