Complicated table join
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.
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
NULL it means that no row exists for this student for this course in the
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
2 Solutions collect form web for “Complicated table join”
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
;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
You want a left outer join. The first table is from the
courses table and is used for the required courses (defined in the
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;