Member Avatar for 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.

  • 3 Contributors
  • 2 Replies
  • 183 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by rch1231
Member Avatar for 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

Member Avatar for 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
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.