Including Scorm Activity name in an SQL Moodle user completion report

Question

How can I extend the excellent SQL code provided here by Russell England: sql code for activity completion in moodle to include the Scorm Activity title (the name entity in the mdl_scorm table) in the report for scorm activities?

I can't find keys to reference the cmc.coursemoduleid entity with the primary key of the mdl_scorm table.

Answer 1

Add m.name = 'scorm' to the join on modules

And add a join to the scorm table using cm.instance for the scorm id

SELECT s.name AS scormname

FROM mdl_course_modules_completion cmc
JOIN mdl_user u ON u.id = cmc.userid
JOIN mdl_course_modules cm ON cm.id = cmc.coursemoduleid
JOIN mdl_course c ON c.id = cm.course

JOIN mdl_modules m ON m.id = cm.module AND m.name = 'scorm'
JOIN mdl_scorm s ON s.id = cm.instance