Select 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select Query

Author

Topic

gj0519
Starting Member

13 Posts

Posted - 2008-08-14 : 13:19:31

I have a select query that returns a student, course, teacher and grading term for 1st and 4th qtr. What I need to do is find students that had a different course & teacher for 4th qtr only. I don't want to see students that have the same teacher. I have tried a few different ways but I am still stuck.
Using Sql Server 2005


select distinct s.studentid, c.description,t.name,so.term 
from student s inner join schedobj so
 on s.studentid = so.studentid inner join section sc
 on so.coursenum = sc.coursenum and so.section = sc.section inner join teacher t
 on sc.teacherid = t.teacherid inner join course c
 on sc.coursenum = c.coursenum 
where c.description = 'Math'
and so.term in ('1', '4')

Thanks,
GJ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 13:38:48

if you have one record per student per quarter may be this is what you want

select  s.studentid
from student s inner join schedobj so
 on s.studentid = so.studentid inner join section sc
 on so.coursenum = sc.coursenum and so.section = sc.section inner join teacher t
 on sc.teacherid = t.teacherid inner join course c
 on sc.coursenum = c.coursenum 
where c.description = 'Math'
and so.term in ('1', '4')
group by s.studentid
having count(distinct teacherid)=2
and count(distinct coursenum)=2

gj0519
Starting Member

13 Posts

Posted - 2008-08-14 : 13:55:45

Usually there is only 1 record per student per quarter, but once in a while a student might switch to a different class/teacher in the same quarter. I will modify my query and see what results I get.

Thanks,

GJ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 14:06:24

quote:
Originally posted by gj0519

Usually there is only 1 record per student per quarter, but once in a while a student might switch to a different class/teacher in the same quarter. I will modify my query and see what results I get.

Thanks,

GJ

can you show some sample data to illustrate the above scenario?

gj0519
Starting Member

13 Posts

Posted - 2008-08-14 : 14:18:12

So what I am after is if term 4 has a different teacher than term 1 meaning the student has switched to a different math course.
These results are 1 record per student per qtr and have the same teacher.


Student		Course	Teacher		Term
430401		MATH	CURTRIGHT	1
430401		MATH	CURTRIGHT	4
430402		MATH	WILSON	        1
430402		MATH	WILSON   	4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 14:28:33

may be this

select  s.studentid
from student s inner join schedobj so
 on s.studentid = so.studentid inner join section sc
 on so.coursenum = sc.coursenum and so.section = sc.section inner join teacher t
 on sc.teacherid = t.teacherid inner join course c
 on sc.coursenum = c.coursenum 
where c.description = 'Math'
and so.term in ('1', '4')
group by s.studentid
having max(case when term=1 then t.name else null end)<>max(case when term=4 then t.name else null end)

gj0519
Starting Member

13 Posts

Posted - 2008-08-14 : 14:34:59

That is what I am after.
Thanks for the assistance.

GJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources