Hi,
I want to obtain the list of all user enrolled in a couse, with, for every user, his final grade in the course. I have very poor experience with DB.
I find two queries that can be useful, but i don't understand how can combine them.
The first one gives the list of partecipants in a course.
SELECT
user.firstname AS Firstname,
user.lastname AS Lastname,
user.email AS Email,
user.city AS City,
course.fullname AS Course
FROM
prefix_user AS user,
prefix_course AS course,
prefix_role_assignments AS asg
INNER JOIN prefix_context AS context ON asg.contextid=context.id
WHERE
context.contextlevel = 50
AND
user.id=asg.userid
AND
context.instanceid=course.id
AND
course.courseid=127
The second one gives final grades for user of a specific course:
SELECT
u.username,
u.firstname,
u.lastname,
gg.finalgrade,
c.fullname,
gi.itemname
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items gi ON gi.id = gg.itemid
JOIN prefix_user u ON u.id = gg.userid
JOIN prefix_course c ON c.id=gi.courseid
WHERE
gi.courseid=127
AND
gi.itemtype='course'