#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    4
    Rep Power
    0

    Parent child select query


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

    theme.sql https://130.95.21.120/md/theme.sql

    strand.sql https://130.95.21.120/md/strand.sql

    year.sql https://130.95.21.120/md/year.sql
  2. #2
  3. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,977
    Rep Power
    9647
    Code:
    SELECT year.year AS name, strand.strand_name AS parent
    FROM year, strand
    JOIN theme ON theme.theme_pk = strand.theme_fk
    You're doing a full join on year+strand, which I'm sure cannot be correct since that will produce every combination possible of the rows in each table. You're also adding in the theme table for no reason that I can see.

    Code:
    SELECT fields
    FROM the main table you want to query
    JOIN possibly another table ON something to relate the main table with this new table

IMN logo majestic logo threadwatch logo seochat tools logo