SQL to obtain all user courses and finalgrade

Picture of iker ibarguren
SQL to obtain all user courses and finalgrade


I´m developing a small script to synchronize our moodle with our ERP and for that I need an SQL to obtain a list of users with ther courses and their final grade in each course. The have courses/grades have to be even from actually enroled courses or past/unroled courses.

I have this SQL so far:

SELECT DISTINCT, mu.firstname, mu.lastname ,, c.fullname, , gg.finalgrade
FROM mdl_grade_items AS gi
INNER JOIN mdl_course c ON = gi.courseid
LEFT JOIN mdl_grade_grades AS gg ON gg.itemid =
INNER JOIN mdl_user AS mu ON gg.userid =
WHERE gi.itemtype = 'course'
ORDER BY desc, asc


But this only shows the courses actually enroled, how can I obtain past or finished courses/grades ?