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.

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

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.