title: Moodle SCORM SQL
body: <p>What table and field I need to use in SQL to find SCORM activities that reports back score to Moodle and exclude the SCORM objects that are set as non scoring. Basically looking for the SCORM settings field: <strong>Require Minimum Score</strong> in SQL</p>
  
  <p>The query:</p>
  
  <pre><code>    SELECT
      CONCAT(u.firstname,' ',u.lastname ) AS 'fullname', 
      cc.name AS 'Category',
      c.fullname AS 'Course', 
      gi.itemname AS 'Item Name',
      ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS 'Percentage',
      asgm.status as 'Status',
      p.status AS 'Completed',
      gi.itemmodule as 'Type',
      s.completionscorerequired AS require_minimum_score
      FROM prefix_course AS c
      JOIN prefix_course_categories AS cc ON cc.id = c.category
      JOIN prefix_context AS ctx ON c.id = ctx.instanceid
      JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
      JOIN prefix_user AS u ON u.id = ra.userid
      JOIN prefix_grade_items AS gi ON gi.courseid = c.id
      JOIN prefix_scorm AS s ON c.id = s.course
      LEFT JOIN prefix_grade_grades AS gg ON gi.id = gg.itemid AND gg.userid = u.id
      LEFT JOIN prefix_course_completions AS p ON p.userid = u.id AND p.course = c.id
      LEFT JOIN (
        SELECT asgm.status as status, c.id as cid, u.id as uid,  gi.id as giid
        FROM prefix_course AS c
             JOIN prefix_course_categories AS cc ON cc.id = c.category
             JOIN prefix_context AS ctx ON c.id = ctx.instanceid
             JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
             JOIN prefix_user AS u ON u.id = ra.userid
             JOIN prefix_grade_items AS gi ON gi.courseid = c.id
             JOIN prefix_assign AS asg ON gi.iteminstance = asg.id and asg.course = c.id
             JOIN prefix_assign_submission AS asgm ON asg.id = asgm.assignment and asgm.userid = u.id 
        ) AS asgm ON c.id = asgm.cid and u.id = asgm.uid  and gi.id = asgm.giid
  
      WHERE (gi.itemmodule = 'quiz' OR gi.itemmodule= 'assign' OR gi.itemmodule= 'scorm') AND c.visible=1
  </code></pre>