sqlquery

[Mary Phelps]

I have a table student with following columns
StudentId SemesterId ExamYearId
1 1 1
1 2 2
1 3 3
3 1 1
3 2 2
3 3 4
7 1 1
7 3 4
8 1 1
8 2 2
I want a query to get all such student for whom no data exists for semesterid=3 and examyearid=3, however same student should have data for semesterid=2. In this case it should return me studentid=8 as studentid=8 has data for semesterid=2 and examyearid=2 however no data for examyearid=3 and semesterid=3. Basically I want to be able to input semesterid and examyearid and find which studentid's have not filled data for that semesterid and examyearid.

[Erland Sommarskog]

Post by Mary Phelps
I have a table student with following columns
StudentId SemesterId ExamYearId
1 1 1
1 2 2
1 3 3
3 1 1
3 2 2
3 3 4
7 1 1
7 3 4
8 1 1
8 2 2
I want a query to get all such student for whom no data exists for
semesterid=3 and examyearid=3, however same student should have data for
semesterid=2. In this case it should return me studentid=8 as
studentid=8 has data for semesterid=2 and examyearid=2 however no data
for examyearid=3 and semesterid=3. Basically I want to be able to input
semesterid and examyearid and find which studentid's have not filled
data for that semesterid and examyearid.

I would expect the table Student to have StudentID as the primary key,
and this table would be called StudentSemesters or some such. This gives:

SELECT S.StudentID
FROM Student S
WHERE EXISTS (SELECT *
FROM StudentSemesters SS1
WHERE S.StudentID = SS1.StudentID
AND SS1.SemesterID = 3)
AND EXISTS (SELECT *
FROM StudentSemesters SS2
WHERE S.StudentID = SS2.StudentID
AND SS2.ExamYearID = 3)
AND NOT EXISTS (SELECT *
FROM StudentSemesters SS3
WHERE S.StudentID = SS3.StudentID
AND SS3.SemesterID = 2)

[Mary Phelps]

I tried your query and it works. However after looking at results I realised I asked the wrong question. I want the studentid where max(semesterid)=3 and max(yearid)=3

[Erland Sommarskog]

Post by Mary Phelps
I tried your query and it works. However after looking at results I
realised I asked the wrong question. I want the studentid where
max(semesterid)=3 and max(yearid)=3

SELECT StudentID
FROM StudentSemesters
GROUP BY StudentID
HAVING max(semesterid)=3 and max(yearid)=3