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.
Best Answer