MySQL Forums
Forum List  »  Newbie

MySQL CONCAT and INNER JOIN - Adding another child table
Posted by: Peter Browne
Date: July 08, 2021 10:03PM

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.

Options: ReplyQuote


Subject
Written By
Posted
MySQL CONCAT and INNER JOIN - Adding another child table
July 08, 2021 10:03PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.