Hi all. I've run into a challenge due to my limited knowledge of SQL queries.
I'm trying to generate a custom report within each course that gives us a list of users including the group they belong to, how they enrolled, and when they completed. I've been getting close, except that the report only gives me the users who belong to a group, but it excludes users who do not belong to any groups. How can I tweak my query to include all users, whether they belong to a group or not?
I'd ideally like the query to return a full list of all active students, and if the don;t belong to a group, have the group field left blank or return "No Groups" like the participant page in Moodle does. We're running Moodle 3.5
Here's the query I'm running:
SELECT c.shortname AS Course, g.name AS Groupname, u.firstname, u.lastname, er.enrol AS enrolmethod, FROM_UNIXTIME(ue.timecreated) AS Enroldate, FROM_UNIXTIME(cc.timecompleted) AS Finishdate
FROM prefix_course AS c
JOIN prefix_groups AS g ON g.courseid = c.id
JOIN prefix_groups_members AS m ON g.id = m.groupid
JOIN prefix_user AS u ON m.userid = u.id
JOIN prefix_enrol AS er ON er.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = er.id AND ue.userid = u.id
JOIN prefix_course_completions AS cc ON cc.course = c.id AND cc.userid = u.id
WHERE c.id = %%COURSEID%%