MySQL - How to modify parent/child select query to add more children to existing array/JSON?

I have the following query working OK:

SELECT core_condition AS name, NULL AS parent
FROM condition_theme_lookup
UNION ALL
SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent
FROM theme, condition_theme_lookup
UNION ALL
SELECT strand.strand_name AS name, theme.theme_name AS parent
FROM strand
JOIN theme ON theme.theme_pk = strand.theme_fk

The resulting array, with some PHP, produces the following JSON, which is fine so far, showing the 'strand' children of 'theme' parents:

{
    "name": "Condition",
    "children": [{
        "name": "Professional",
        "children": [{
            "name": "Professional Behavours"
        }, {
            "name": "Self-Care and Self-Awareness"
        }, {
            "name": "Medical Ethics and Law"
        }]
    }, {
        "name": "Leader",
        "children": [{
            "name": "Teamwork and Leadership"
        }, {
            "name": "Collaborative Practice"
        }, {
            "name": "Health Systems and Careers"
        }]
    }, {
        "name": "Advocate",
        "children": [{
            "name": "Health Advocacy"
        }, {
            "name": "Aboriginal Health"
        }, {
            "name": "Diversity and Inequality"
        }, {
            "name": "Health Promotion"
        }]
    }, {
        "name": "Clinician",
        "children": [{
            "name": "Scientific Knowledge"
        }, {
            "name": "Patient Assessment and Clinical Reasoning"
        }, {
            "name": "Patient Management"
        }, {
            "name": "Patient Perspective"
        }, {
            "name": "Clinical Communication"
        }, {
            "name": "Quality Care"
        }]
    }, {
        "name": "Educator",
        "children": [{
            "name": "Life-Long Learning"
        }, {
            "name": "Mentoring Relationships"
        }, {
            "name": "Patient Education"
        }, {
            "name": "Teaching and Learning"
        }, {
            "name": "Assessment and Evaluation"
        }]
    }, {
        "name": "Scholar",
        "children": [{
            "name": "Research and Biostatistics"
        }, {
            "name": "Evidence-Based Practice"
        }, {
            "name": "Information Literacy"
        }]
    }]
}

I now want to add the same children set: 'Year 1', 'Year 2', 'Year 3' and 'Year 4', from table strand.year, to each strand.strand_name parent (e.g. Professional Behaviours, Medical Ethics and Law etc).

I have tried the following modified query:

SELECT core_condition AS name, NULL AS parent
FROM condition_theme_lookup
UNION ALL
SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent
FROM theme, condition_theme_lookup
UNION ALL
SELECT strand.strand_name AS name, theme.theme_name AS parent
FROM strand, theme
UNION ALL
SELECT strand.year AS name, strand.strand_name AS parent
FROM strand
JOIN theme ON theme.theme_pk = strand.theme_fk

But as you can see below, the relationships now are incomplete; the first five nodes have lost their children, and only one strand, Information Literacy, has the Year children.

   {
    "name": null,
    "children": [{
        "name": "Professional"
    }, {
        "name": "Leader"
    }, {
        "name": "Advocate"
    }, {
        "name": "Clinician"
    }, {
        "name": "Educator"
    }, {
        "name": "Scholar",
        "children": [{
            "name": "Professional Behavours"
        }, {
            "name": "Self-Care and Self-Awareness"
        }, {
            "name": "Teamwork and Leadership"
        }, {
            "name": "Collaborative Practice"
        }, {
            "name": "Health Systems and Careers"
        }, {
            "name": "Health Advocacy"
        }, {
            "name": "Aboriginal Health"
        }, {
            "name": "Diversity and Inequality"
        }, {
            "name": "Health Promotion"
        }, {
            "name": "Scientific Knowledge"
        }, {
            "name": "Patient Assessment and Clinical Reasoning"
        }, {
            "name": "Patient Management"
        }, {
            "name": "Patient Perspective"
        }, {
            "name": "Clinical Communication"
        }, {
            "name": "Quality Care"
        }, {
            "name": "Life-Long Learning"
        }, {
            "name": "Mentoring Relationships"
        }, {
            "name": "Patient Education"
        }, {
            "name": "Teaching and Learning"
        }, {
            "name": "Assessment and Evaluation"
        }, {
            "name": "Research and Biostatistics"
        }, {
            "name": "Evidence-Based Practice"
        }, {
            "name": "Information Literacy",
            "children": [{
                "name": "Year 1"
            }, {
                "name": "Year 2"
            }, {
                "name": "Year 3"
            }, {
                "name": "Year 4"
            }]
        }, {
            "name": "Medical Ethics and Law"
        }]
    }]
}

How should the query be changed to show all the relationships as in the first JSON, and add the set of four 'Year X' children to each strand?

Required JSON result up to Year children (ignore children of Year x

See fiddle for original query

SQL:

theme.sql

strand.sql

from MySQL - How to modify parent/child select query to add more children to existing array/JSON?