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.
Author |
Topic |
giorgosP
Starting Member
1 Post |
Posted - 2013-09-29 : 21:38:07
|
I am trying to build a query in sql.The relations areTEACHERS(Tid, Name, LastName, Gender, Scale)COURSES(Cid, Title, Type, Grades)ASSIGNMENTS(Tid, Cid, Semester)where Tid is the id of teacher and Cid is the id of course and what i am trying to do is to give a query for retrieving the id and the last name of teachers who taught the most courses during the second semester.I have tried this but i dont think is correct. I dont have a way to test it on sql server, so i dont know if it is correct. Does anybody have an idea?SELECT Tid, LastName FROM TEACHERS WHERE Tid IN ( SELECT Tid FROM ASSIGNMENTS WHERE Semester=2 GROUP BY Tid HAVING MAX( SELECT COUNT(Cid) FROM COURSES AS C, ASSIGNMENTS AS A WHERE A.Cid=C.Cid AND A.Semester=2 GROUP BY Cid ) ) |
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2013-09-30 : 00:45:00
|
Mention Having Max() Value Is <> Some Value.SELECT Tid, LastName FROM TEACHERS WHERE Tid IN ( SELECT Tid FROM ASSIGNMENTS WHERE Semester=2 GROUP BY Tid HAVING MAX((SELECT COUNT(C.Cid) FROM COURSES AS C,ASSIGNMENTS AS A WHERE A.Cid=C.Cid AND A.Semester=2 GROUP BY Cid )) <> 0 )veeranjaneyulu |
|
|
johnson.tumble9
Starting Member
1 Post |
Posted - 2013-09-30 : 03:51:29
|
hi everyone i love this site and i have visited this great website on fixed interval of time my maximum problem are solved from here. from their i have make a new website for content writing please visit[url=http://www.technobooster.tk] Article, blogs and content writing[/url]Right |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-30 : 09:59:17
|
quote: Originally posted by giorgosP I am trying to build a query in sql.The relations areTEACHERS(Tid, Name, LastName, Gender, Scale)COURSES(Cid, Title, Type, Grades)ASSIGNMENTS(Tid, Cid, Semester)where Tid is the id of teacher and Cid is the id of course and what i am trying to do is to give a query for retrieving the id and the last name of teachers who taught the most courses during the second semester.I have tried this but i dont think is correct. I dont have a way to test it on sql server, so i dont know if it is correct. Does anybody have an idea?SELECT Tid, LastName FROM TEACHERS WHERE Tid IN ( SELECT Tid FROM ASSIGNMENTS WHERE Semester=2 GROUP BY Tid HAVING MAX( SELECT COUNT(Cid) FROM COURSES AS C, ASSIGNMENTS AS A WHERE A.Cid=C.Cid AND A.Semester=2 GROUP BY Cid ) )
SELECT TOP 1 WITH TIES t.*FROM TEACHERS tINNER JOIN (SELECT Tid,COUNT(Cid) AS Cnt FROM ASSIGNMENTS WHERE Semester = 2 )aON a.Tid = t.TidORDER BY Cnt DESC you ca make it more generic by using a parameter for Semester and then passing any values for it to get details for 1st, 3rd ,4th etc semesters------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|