Sql-server – Joins on multiple tables

join;sql server

Given this schema, is it possible to produce an output wherein it shows all the User's assigned modules with its matching enrollment id if any, and NULL if none?

Users
+----+------+----------+
| id | name | group_id |
+----+------+----------+
| 1  | Carl | 1        |
+----+------+----------+
| 2  | Joe  | 1        |
+----+------+----------+
| 3  | Dan  | 2        |
+----+------+----------+

Modules
+----+---------+
| id | name    |
+----+---------+
| 1  | Alpha   |
+----+---------+
| 2  | Bravo   |
+----+---------+
| 3  | Charlie |
+----+---------+
| 4  | Delta   |
+----+---------+
| 5  | Echo    |
+----+---------+

Approved_Courses
+----+----------+--------+--------------+
| id | group_id | module | order_number |
+----+----------+--------+--------------+
| 1  | 1        | 1      | 1            |
+----+----------+--------+--------------+
| 2  | 1        | 2      | 2            |
+----+----------+--------+--------------+
| 3  | 1        | 3      | 3            |
+----+----------+--------+--------------+
| 4  | 2        | 1      | 1            |
+----+----------+--------+--------------+
| 5  | 2        | 3      | 2            |
+----+----------+--------+--------------+
| 6  | 2        | 2      | 3            |
+----+----------+--------+--------------+

Classes
+----+--------+
| id | module |
+----+--------+
| 1  | 1      |
+----+--------+
| 2  | 2      |
+----+--------+

Enrollments
+--------+----------+----------+
| id     | staff_id | class_id |
+--------+----------+----------+
| ENR-1  | 1        | 1        |
+--------+----------+----------+

My desired output would be below. I can't seem to do it without unnecessary duplicates and/or wrong information on enrollment.id

+----------+---------------+------------+---------------------+------------+
| users.id | user.group_id | modules.id | module.order_number | enrollment |
+----------+---------------+------------+---------------------+------------+
| 1        | 1             | 1          | 1                   | ENR-1      |
+----------+---------------+------------+---------------------+------------+
| 1        | 1             | 2          | 2                   | NULL       |
+----------+---------------+------------+---------------------+------------+
| 1        | 1             | 3          | 3                   | NULL       |
+----------+---------------+------------+---------------------+------------+
| 2        | 1             | 1          | 1                   | NULL       |
+----------+---------------+------------+---------------------+------------+
| 2        | 1             | 2          | 2                   | NULL       |
+----------+---------------+------------+---------------------+------------+
| 2        | 1             | 3          | 3                   | NULL       |
+----------+---------------+------------+---------------------+------------+
| 3        | 2             | 1          | 1                   | NULL       |
+----------+---------------+------------+---------------------+------------+
| 3        | 2             | 2          | 3                   | NULL       |
+----------+---------------+------------+---------------------+------------+
| 3        | 2             | 3          | 2                   | NULL       |
+----------+---------------+------------+---------------------+------------+

I would appreciate any help.

http://www.sqlfiddle.com/#!9/d3cc9d5/1

Best Answer

SELECT users.id,users.group_id,Approved_Courses.module 
      ,Approved_Courses.order_number , Enrollments.id
FROM 
Users full outer join 
approved_courses
  on users.group_id=approved_courses.group_id
left outer  join Classes 
  on Classes.id=Approved_Courses.group_id and Approved_Courses.module=Classes.module 
left outer join Enrollments
  on Classes.id=Enrollments.class_id 
 and Users.id=Enrollments.staff_id

order by users.id,users.group_id
        ,Approved_Courses.module ,Approved_Courses.order_number 

enter image description here