Skip to content

Students, and the Courses They Never Started

April 27, 2011

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

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: