help me writing the query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help me writing the query

Author

Topic

pv.suresh
Starting Member

6 Posts

Posted - 2007-11-27 : 00:09:35

Hi all,

I am not an expert in writing sql queries. Please help me writing this sql query.

I have a table with two columns like this.

teacher_id --student_id

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

Now i need only teacher_id or teacher_id's for which minimum number of students assigned.

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 00:11:15

select top 1 teacher_id
from table t
group by teacher_id
order by count(*)

KH
[spoiler]Time is always against us[/spoiler]

pv.suresh
Starting Member

6 Posts

Posted - 2007-11-27 : 00:19:45

Thanks KH....

But if two teachers have same number of students.... I want those two teacher id's.

In the query that u given, it won't return two teacher_id's!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 00:36:58

[code]SELECT t.teacher_id
FROM TABLE t
GROUP BY t.teacher_id
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*) FROM TABLE x GROUP BY teacher_id ORDER BY COUNT(*))[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 00:41:26

or if you are using SQL Server 2005,

DECLARE @sample TABLE
(
	teacher_id	int,
	student_id	int
)

INSERT INTO @sample
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

;WITH data(teacher_id, cnt) AS
(
	SELECT	s.teacher_id, cnt = COUNT(*)
	FROM	@sample s
	GROUP BY s.teacher_id
)
SELECT	d.teacher_id
FROM	data d
WHERE	cnt = (SELECT MIN(cnt) FROM data)

KH
[spoiler]Time is always against us[/spoiler]

pv.suresh
Starting Member

6 Posts

Posted - 2007-11-27 : 01:06:06

Actually i will get teacher_id and student_id by joining two different tables....

That means teacher table and student table are different and teacher_id is the foreign key for student table.

So if i execute the query

SELECT t.teacher_id
FROM TABLE t
GROUP BY t.teacher_id
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*) FROM TABLE x GROUP BY teacher_id ORDER BY COUNT(*))

I am doing two joins here in this query. One is main join and the other is the join in sub query.

So i am searching for the query which can eliminate this situation.

I am using MSSQL.... So the other option is possible.

pv.suresh
Starting Member

6 Posts

Posted - 2007-11-27 : 01:12:49

In short my requirement is some thing like this....

I should be able to write a query similar to this.

select teacher_id from t
group by t.teacher_id
where count(*) is minimum.

But aggregate functions in where clause are not allowed....

Can u help me to resolve this problem...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 01:14:33

see my post

KH
[spoiler]Time is always against us[/spoiler]

pv.suresh
Starting Member

6 Posts

Posted - 2007-11-27 : 01:33:28

Hi KH...

I am talking about your recent post

SELECT t.teacher_id
FROM TABLE t
GROUP BY t.teacher_id
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*) FROM TABLE x GROUP BY teacher_id ORDER BY COUNT(*))

In this query... I have to use two joins as my teacher table and student table are different.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 01:36:37

[code]SELECT t.teacher_id
FROM Teacher t
GROUP BY t.teacher_id
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*)
FROM Teacher x INNER JOIN Student y
ON x.teacher_id = y.teacher_id
GROUP BY x.teacher_id ORDER BY COUNT(*))[/code]

KH
[spoiler]Time is always against us[/spoiler]

pv.suresh
Starting Member

6 Posts

Posted - 2007-11-27 : 04:23:28

Let's say i can have a case as given below.

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.

If i run the above query it will return no records. Because in the above query we are looking at the count that a teacher_id occur's in the teacher's table. That's not the requirement. Instead we should look in teacher RIGHT OUTER JOIN student table.

So the query should look like below.

SELECT t.teacher_id
FROM Teacher t RIGHT OUTER JOIN Student y
GROUP BY t.teacher_id
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*)
FROM Teacher x RIGHT OUTER JOIN Student y
ON x.teacher_id = y.teacher_id
GROUP BY x.teacher_id ORDER BY COUNT(*))

But the problem is we are using two joins as a whole.
Is there any other way to write the same query....

NOTE: two table's should be right outer joined because there can exists teacher_id's with no students assigned to them

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 04:36:46

[code]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 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 JOIN @students s
ON t.teacher_id = s.teacher_id
GROUP BY t.teacher_id
ORDER BY COUNT(s.teacher_id)
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources