The query below creates a hierarchical relationship with tables and records:
program -> accreditation_standard_group -> accreditation_standard -> learning_event
Base on the following tables structures:
+-----------------------------+
| program_pk | program_name |
+-----------------------------+
+---------------------------------+-------------------------------+------------+
| accreditation_standard_group_pk | accreditation_standard_group | program_fk |
+---------------------------------+-------------------------------+------------+
+---------------------------+------------------------+---------------------------------+
| accreditation_standard_pk | accreditation_standard | accreditation_standard_group_fk |
+---------------------------+------------------------+---------------------------------+
+-------------------+----------------------+---------------------------+
| learning_event_pk | learning_event_name | accreditation_standard_fk |
+-------------------+----------------------+---------------------------+
I now want to add another table as a sibling of table `learning_event` so that the relationship becomes:
program -> accreditation_standard_group -> accreditation_standard -> learning_event
-> assessment
That is, tables `learning_event` and `assessment` have the parent `accreditation_standard`
Note that both table `learning_event` and table `assessment` have relationships to table `accreditation_standard` using the columns `accreditation_standard_fk` (which is not a true foreign_key...)
The original query, which works fine, but does not contain table `assessment` as a sibling of table `learning_event` is:
SELECT CONCAT('program:', program_pk) AS global_id,
program_name AS name,
NULL AS parent_global_id
FROM program
UNION ALL
SELECT CONCAT('accreditation_standard_group:', accreditation_standard_group_pk) AS global_id,
accreditation_standard_group AS name,
CONCAT('program:', program_fk) AS parent_global_id
FROM accreditation_standard_group
UNION ALL
SELECT
CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard) AS global_id,
accreditation_standard AS name,
CONCAT('accreditation_standard_group:', accreditation_standard_group_fk) AS parent_global_id
FROM accreditation_standard
UNION ALL
SELECT
CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard, ',learning_event:', learning_event_name) AS global_id,
learning_event_name AS name,
CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard) AS parent_global_id
FROM learning_event le
INNER JOIN accreditation_standard ass ON ass.accreditation_standard_pk = le.accreditation_standard_fk
INNER JOIN accreditation_standard_group asg ON ass.accreditation_standard_group_fk = asg.accreditation_standard_group_pk
How should I add the table assessment as required above? Can this be added in the last `CONCAT` with an extra `INNER JOIN`? Or does it need another `CONCAT` as well?
An example of the working code would be great. See
https://www.db-fiddle.com/f/6PiURUaDwpdaXHmHCiJjsB/0
NOTE: I need to retain the format of the original query, just need to add the extra table as sibling as explained.