title: MySQL CONCAT and INNER JOIN - Adding another child table
body: <p>The query below creates a hierarchical relationship with tables and records:</p>
  <pre><code>program -&gt; accreditation_standard_group -&gt; accreditation_standard -&gt; learning_event
  </code></pre>
  <p>Base on the following tables structures:</p>
  <pre><code>+------------+----------------+
  | 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 |            
  +-------------------+----------------------+---------------------------+
  </code></pre>
  <p>I now want to add another table as a sibling of table <code>learning_event</code> so that the relationship becomes:</p>
  <pre><code>program -&gt; accreditation_standard_group -&gt; accreditation_standard -&gt; learning_event
                                                                    -&gt; assessment
  </code></pre>
  <p>That is, tables <code>learning_event</code> and <code>assessment</code> have the parent <code>accreditation_standard</code></p>
  <pre><code>+---------------+-------------+---------------------------+
  | assessment_pk |  assessment | accreditation_standard_fk |            
  +---------------+-------------+---------------------------+
  </code></pre>
  <p>Note that both table <code>learning_event</code> and table <code>assessment</code> have relationships to table <code>accreditation_standard</code> using the columns <code>accreditation_standard_fk</code> (which is not a true foreign_key...)</p>
  <p>The original query, which works fine, See <a href="https://www.db-fiddle.com/f/6PiURUaDwpdaXHmHCiJjsB/0" rel="nofollow noreferrer"><strong>db-fiddle</strong></a>, but does not contain table <code>assessment</code> as a sibling of table <code>learning_event</code> is:</p>
  <pre><code>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
  </code></pre>
  <p>How should I add the table assessment as required above?  Can this be added in the last <code>CONCAT</code> with an extra <code>INNER JOIN</code>?  Or does it need another <code>CONCAT</code> as well?</p>
  <p>An example of the working code would be great.  See <a href="https://www.db-fiddle.com/f/6PiURUaDwpdaXHmHCiJjsB/0" rel="nofollow noreferrer"><strong>db-fiddle</strong></a></p>
  <p>NOTE:  I need to retain the format of the original query, just need to add the extra table as sibling as explained.</p>