title: Query for student's marks and grades in moodle
body: <p>I'm trying to get a student's marks and grades with a query but I can't work out joins. It is very complex database. Has anyone done it before?</p>
  
  <p>The query below (tables modified by myself) works for some student but not for others so I need a proper one with original table structure.</p>
  
  <p>Thanks</p>
  
  <p><strong>MY FAULTY QUERY</strong></p>
  
  <pre><code>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
    INNER JOIN mdl_grade_grades 
      ON mdl_grade_items.id = mdl_grade_grades.itemid
    INNER JOIN mdl_role_assignments 
      ON mdl_grade_grades.userid = mdl_role_assignments.userid 
      AND mdl_grade_items.courseid = mdl_role_assignments.mdlcourseid
    INNER JOIN mdl_course 
      ON mdl_course.id = mdl_grade_items.courseid
    INNER JOIN mdl_user 
      ON mdl_user.id = mdl_role_assignments.userid
  WHERE mdl_grade_items.courseid = '2864' 
    AND mdl_user.username = '123456789'
  </code></pre>