0

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

  • Fiddle structure/data do not match published in a question. At least in Enrollments.ID. Synchronize please. – Akina Apr 24 at 5:38
  • Updated record values on Approved_Courses.id, and synced values on the fiddle provided. – xSkyyy16 Apr 25 at 12:26
1
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.