MySQL - Parent child select query

by IlludiumPu36   Last Updated May 16, 2019 04:06 AM

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

As JSON, this produces the following which is fine so far:

{
    "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 modify this query to add the children 'Year 1', 'Year 2', 'Year 3' and 'Year 4' (from table year.year) to each strand 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 year.year AS name, strand.strand_name AS parent
FROM year, strand
JOIN theme ON theme.theme_pk = strand.theme_fk

but it's not producing the correct relationships:

{
    "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"
        }, {
            "name": "Medical Ethics and Law",
            "children": [{
                "name": "Year 1"
            }, {
                "name": "Year 2"
            }, {
                "name": "Year 3"
            }, {
                "name": "Year 4"
            }]
        }]
    }]
}

SQL:

theme.sql

strand.sql

year.sql

Tags : mysql


Related Questions


Updated November 15, 2016 08:02 AM

Updated April 09, 2015 22:02 PM

Updated August 24, 2018 15:06 PM

Updated December 27, 2018 07:06 AM

Updated June 11, 2015 18:02 PM