0

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 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 in the below JSON, the relationships aren't correct. The first five nodes have lost their children, and only one strand, Information Literacy, has the Year children. Why has the modified query broken this JSON?

   {
    "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 would I use the year column in table strand as an extra join, if that is required?

Otherwise, how should I get the full JSON to display with all strands having the four Year children?

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

See fiddle

SQL:

theme.sql

strand.sql

  • Now please show desured result as it must be obtained in a query (in table form, not as JSON). And specify MySQL version of YOUR server. Additionally I recommend to decrease the number of records in a fiddle (2 records in theme and 8-10 in strand seems to be enough). – Akina May 16 at 8:56
  • You removed a join in the 2nd query: FROM strand JOIN theme ON theme.theme_pk = strand.theme_fk is missing from the 3rd subquery and it has become a cross join – ypercubeᵀᴹ May 16 at 10:44
  • Could you elaborate, perhaps show the whole query as you think it should be? – IlludiumPu36 May 17 at 2:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.