Foreign Key MySQL - Moodle

Question

i have a table, Categories, it has a column 'id' (PK) and the same table has the column parent which is a FK. Table

What i need (and can't) is to take the 'parent' id and show the value 'name' associated to this id, when i select 'name' it doesn't show me 'parent' name but 'id' name.

Structure

Please let me know if the information is enough.

Thanks for your time.

That's right Nick, i wanted query the database. Russell answer worked! Thanks your for taking the time to reply. — Juan_P, Dec 11 '21 at 12:51

score 0 · Accepted Answer · answered Dec 10 '21 at 12:00

0

SELECT c.id AS courseid, c.fullname AS coursename,
    cat.id AS catid, cat.name AS catname,
    cat.parent AS parentcatid,
    CASE
        WHEN cat.parent = 0 THEN 'Root Category'
        ELSE parentcat.name
    END AS parentcatname
FROM mdl_course c
JOIN mdl_course_categories cat ON cat.id = c.category
LEFT JOIN mdl_course_categories parentcat ON parentcat.id = cat.parent

answered Dec 10 '21 at 12:00

Russell England

9,436
1
27
41

Super, would you mind marking the answer as correct? :) – Russell England Dec 11 '21 at 14:48
Done! (I didn't know I should marked the correct answer, thanks again) – Juan_P Dec 12 '21 at 17:50

Foreign Key MySQL - Moodle

1 Answers1