Complicated table join

Question

I thought I had a good grasp on table joins but there is one problem here I can't figure out.

I am trying to track the progress of students on specifically required courses. Some students are required to complete an exact list of courses before further qualification.

Tables (simplified):

students
--------
id INT PRIMARY KEY
name VARCHAR(50)

student_courses
---------------
student_id INT PRIMARY KEY
course_id TINYINT PRIMARY KEY
course_status TINYINT (Not done, Started, Completed)
steps_done TINYINT
total_steps TINYINT
date_created DATETIME
date_modified DATETIME

courses
-------
id TINYINT PRIMARY KEY
name VARCHAR(50)

I want to insert a list of required courses, for example 5 different courses in the courses table and then select a specific student and get list of all the courses required, whether a row exists for that course in the student_courses table or not.

I guess I could insert all rows from the courses table in the student_courses table for each student, but I don't want that because not all students need to do these courses. And what if new courses are added later.

I just want a result which is something like this:

students table:

id  name
--- ------------------
1   George Smith
2   Dana Jones
3   Maria Cobblestone

SELECT * FROM students (JOIN bla bla bla - this is the point where I'm lost...)
WHERE students.id = 1

Result:

id  name                course_id  courses.name  course_status  steps_done
--- ------------------  ---------  ------------  -------------  ----------
1   George Smith        1          Botany        Not started    0
1   George Smith        2          Biology       NULL           NULL
1   George Smith        3          Physics       NULL           NULL
1   George Smith        4          Algebra       Completed      34
1   George Smith        5          Sewing        Started        2

If the course_status or steps_done is NULL it means that no row exists for this student for this course in the student_courses table.

The idea is then using this in MS Access (or some other system) and have the row automatically inserted in the student_courses table once you enter a value in the NULL field.

Answer 1

You want a left outer join. The first table is from the courses table and is used for the required courses (defined in the where clause).

select s.id, s.name, c.id, c.name, c.course_status, c.steps_done
from (courses as c left join
      student_courses as sc
      on sc.course_id = c.id and
         sc.student_id = 1
     ) left join
     students as s
     on sc.student_id = s.id
where c.id in (<list of required courses>)
order by s.id, c.id;

I think I have all the "Access"isms in there.

Actually, the above will be missing the student name when s/he is missing a course. The following is more correct:

select s.id, s.name, c.id, c.name, c.course_status, c.steps_done
from (courses as c left join
      student_courses as sc
      on sc.course_id = c.id and
         sc.student_id = 1
     ) cross join
     students as s
     on s.id = 1
where c.id in (<list of required courses>)
order by s.id, c.id;

Answer 2

You can't just use an outer join to do this, you need to create a list of all students/classes combinations that you're interested in first, then use that list in a LEFT JOIN. Can be done in a cte/subquery using CROSS JOIN:

;WITH cte AS (SELECT DISTINCT s.id Student_ID
                             ,s.name
                             ,c.id Course_ID
                             ,c.name Class_Name
              FROM Students s
              CROSS JOIN Courses c)
SELECT cte.*,sc.status
FROM cte
LEFT JOIN student_courses sc
  ON cte.course_id = sc.course_id

Can also use a subquery if needs to be done in Access (not 100% on syntax in Access):

SELECT sub.*,sc.status
FROM (SELECT DISTINCT s.id Student_ID
                     ,s.name
                     ,c.id Course_ID
                     ,c.name Class_Name
              FROM Students s
              CROSS JOIN Courses c
     ) AS sub
LEFT JOIN student_courses sc
  ON sub.course_id = sc.course_id

Demo: SQL Fiddle