Examination
Date: Tue, 11 Feb 2003 07:27:59 GMT
Message-ID: <3C12a.104860$ZE.2948691_at_twister2.libero.it>
I'd like to know if somebody can tell me how to do this:
I have such a db:
Student (IdStudent, Name, Surname, Address) DegreeThesis (IdThesis, Tětle, IdTeacher, IdStudent, Mark, Year) Teacher (IdTeacher, Surname)
What I'm asked to find is
all the IdThesis that have got the best mark for each year for each teacher who's now linked to (at least) two theses not yet evaluated (it means mark =0).
I can find rightly all the teacher and all best marks, but I can't link them correctly to their theses: it selects IdThesis related to a correct teacher also if the mark is not the best of that year, but it is present in the sq Mark IN.
This is my SQL code:
SELECT IdThesis FROM
(SELECT IdThesis, IdTeacher, Mark, Year FROM DegreeThesis WHERE IdTeacher IN
(SELECT IdTeacher FROM
(SELECT IdTeacher , COUNT(*) FROM DegreeThesis WHERE Mark= 0 GROUP BY IdTeacher HAVING COUNT(*)>=2)
)
(SELECT MaxMark FROM (SELECT IdTeacher , Max(Mark) AS MaxMark ,Year
);
FROM DegreeThesis WHERE Mark<> 0 GROUP BY IdTeacher , Year )
You've been so great in past..I hope you're great again...
Many thanks
A newbie Received on Tue Feb 11 2003 - 08:27:59 CET