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.id, mu.firstname, mu.lastname ,c.id, c.fullname, gg.id , gg.finalgrade
FROM mdl_grade_items AS gi
INNER JOIN mdl_course c ON c.id = gi.courseid
LEFT JOIN mdl_grade_grades AS gg ON gg.itemid = gi.id
INNER JOIN mdl_user AS mu ON gg.userid = mu.id
WHERE gi.itemtype = 'course'
ORDER BY mu.id desc, c.id asc
But this only shows the courses actually enroled, how can I obtain past or finished courses/grades ?