From: Clairwil <clairwil_at_hotmail.com>
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, IdTeacher, Mark, Year FROM DegreeThesis     WHERE IdTeacher IN


            (SELECT IdTeacher , COUNT(*) FROM DegreeThesis WHERE Mark= 0 GROUP BY IdTeacher HAVING COUNT(*)>=2)

    AND Mark IN

(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

Original text of this message