Students, and the Courses They Never Started

I struggled with this SQL problem a bit longer than it probably should have taken. Hopefully this post prevents another from doing the same.

The table structure I was working with contained a ‘student-table’, ‘course-table’, and a ‘merge-table-with-extra-data’. A pretty basic many-to-many relationship between Students and Courses, along with a date for when each relationship started.


CREATE TABLE `Students` (
`student_id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` varchar(128) DEFAULT NULL,
PRIMARY KEY (`student_id `),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `StudentCourses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
`date_started` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Courses` (
`course_id` int(11) NOT NULL AUTO_INCREMENT,
`lesson_name` varchar(128) DEFAULT NULL,
PRIMARY KEY (`course_id `),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


What I needed from this was a list of students, and the courses they did not start between two given dates. This was a seemingly easy problem, until I actually tried to accomplish the task.

student_name	lesson_name
Doug Burchard	Bull Frog Mating Rituals
Doug Burchard	Worm Tunneling and Moles
John Green	Nest Building for Birds
Susan Archer	Swimming with Sharks
Travis Dodge	Swimming with Sharks

The solution: First, I created a sub-select of every possible student/course combination; then I created a second sub-select of every existing row in StudentCourses created between the two given dates; finally, I left outer joined the first sub-select to the second by both the student_id and course_id, and retained every row where this join had failed.


SELECT m.`student_name`, m.`lesson_name`

FROM (SELECT s.`student_id`, s.`student_name`, c.`course_id, c.`lesson_name`
FROM `Courses` AS c
LEFT JOIN `Students` AS s ON 1 = 1) AS m

LEFT OUTER JOIN (SELECT `id`, `course_id`, `student_id`
FROM `StudentCourses`
WHERE `date_started `>'2010-7-1 AND `date_started `<'2010-8-1') AS sc ON m.`course_id` = sc.`course_id` AND m.`student_id` = sc.`student_id`

WHERE sc.`id` IS NULL