MySQL CONCAT and INNER JOIN – Adding another child table

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

+---------------+-------------+---------------------------+
| assessment_pk |  assessment | accreditation_standard_fk |            
+---------------+-------------+---------------------------+

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, See db-fiddle, 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 db-fiddle

NOTE: I need to retain the format of the original query, just need to add the extra table as sibling as explained.

Answer

Would another UNION ALL in the end provide what you want?

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
UNION ALL
SELECT 
       CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard, ',assessment:', assessment) AS global_id,
       assessment AS name,
       CONCAT('accreditation_standard_group:', accreditation_standard_group_fk, ',accreditation_standard:', accreditation_standard) AS parent_global_id
FROM assessment assess
INNER JOIN accreditation_standard ass ON ass.accreditation_standard_pk = assess.accreditation_standard_fk
INNER JOIN accreditation_standard_group asg ON ass.accreditation_standard_group_fk = asg.accreditation_standard_group_pk