Exist a lot of topic about the different queries for looking marks in SQL database but neither specified the version of Moodle. Actually i have a Moodle v2.62 I'm trying to get a student's grades with a query but I can't work out joins.
The query below (tables found it in stackoverflow).
Default:
SELECT mdl_grade_items.id AS ItemID,
mdl_course.shortname AS CourseShortname,
mdl_grade_items.itemname AS ItemName,
mdl_grade_items.grademax AS ItemGradeMax,
mdl_grade_items.aggregationcoef AS ItemAggregation,
mdl_grade_grades.finalgrade AS FinalGrade,
mdl_user.username AS StudentID,
mdl_user.id
FROM mdl_grade_items
INNERJOIN mdl_grade_grades
ON mdl_grade_items.id = mdl_grade_grades.itemid
INNERJOIN mdl_role_assignments
ON mdl_grade_grades.userid = mdl_role_assignments.userid
AND mdl_grade_items.courseid = mdl_role_assignments.mdlcourseid
INNERJOIN mdl_course
ON mdl_course.id = mdl_grade_items.courseid
INNERJOIN mdl_user
ON mdl_user.id = mdl_role_assignments.userid
WHERE mdl_grade_items.courseid ='2864'AND mdl_user.username ='123456789'
The other user recommended this query.
But the problem i don`t know. What's do you refer with: userid; studentid; itemname; itengrademax; itemaggregation, courseid??
SELECT
u.id AS userid,
u.username AS studentid,
gi.id AS itemid,
c.shortname AS courseshortname,
gi.itemname AS itemname,
gi.grademax AS itemgrademax,
gi.aggregationcoef AS itemaggregation,
g.finalgrade AS finalgrade
FROM mdl_user u
JOIN mdl_grade_grades g ON g.userid = u.id
JOIN mdl_grade_items gi ON g.itemid = gi.id
JOIN mdl_course c ON c.id = gi.courseid
WHERE gi.courseid =:courseid AND u.username =:username