MySQL Forums
Forum List  »  General

Parent child select query
Posted by: Peter Browne
Date: May 15, 2019 09:47PM

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"
}]
}]
}]
}

Options: ReplyQuote


Subject
Written By
Posted
Parent child select query
May 15, 2019 09:47PM


Sorry, you can't reply to this topic. It has been closed.

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.