SQL Query Help

[joshmac]

Hello, I need to run a report against a database, and I am having a difficult time thinking of a way to run a particular report. What I need to do is run a report which selects students that have no course registrations for three consecutive semesters.

The semester table includes the following columns:
semesterID,
semesterCode,
semesterName,
semStartDate,
semEndDate

The course registration table includes the following columns:
ID,
stuID,
course,
semesterCode,
credits,
regDate

I need help getting started because I am not sure where to begin to tackle this. Any help or direction is greatly appreciated.

[Patrick Thomas]

I think you need to reference your students table as well something like -

SELECT id FROM students LEFT OUTER JOIN courses ON students.id=courses.stID WHERE courses.regdate > YOURDATEHERE and courses.id IS NULL

[rch1231]

Hello,

Option 1
How about a little reverse logic and using a sub-query. Run a query that select all unique student id's that do have a course registration in the time frame and then select all students not in that list.

select * 
from student 
where student_id not in (select 
distinct student_id from students 
where course in last 3 semesters)

Option 2
Use what patric suggested but instead of

and courses.id IS NULL 

use

having course.id is null