1

Hya Gang!

I am very close to figuring out a report I can run ad hoc to find all our users who have not enrolled in a course. I do have a report for enrolled people that have not completed the course, but this search is not finding a given student who is not even enrolled.

The current code is

SELECT u.lastname, u.firstname , u.email , c.fullname, 

DATE_FORMAT(FROM_UNIXTIME(cc.timecompleted),'%m/%d/%Y %T') AS 'Completed'

FROM 
prefix_role_assignments AS ra
JOIN prefix_context AS context ON context.id = ra.contextid 

AND
context.contextlevel = 50 JOIN prefix_course AS c ON c.id = context.instanceid

AND
 c.fullname LIKE "SAMPLE_COURSE_NAME" 
JOIN prefix_user AS u ON u.id = ra.userid 
JOIN prefix_course_completions AS cc ON cc.course = c.id 
AND cc.userid = u.id

ORDER BY
cc.timecompleted,
u.lastname,
u.firstname

Any thoughts??

DKeeler
  • 33
  • 3
  • Convert your inner join to a left join on the course tables. The Join you are using is defaulting to an inner join and mandates all columns in the joins have a match on each table – SCCJS Oct 12 '21 at 17:44

2 Answers2

2

This will list all users not enrolled in a course - could be a big list though.

Replace xxx with the course id

SELECT u.id AS userid, u.firstname, u.lastname, u.email
FROM mdl_user u
WHERE u.deleted = 0
AND u.suspended = 0
AND NOT EXISTS (
    SELECT ue.userid
    FROM mdl_user_enrolments ue
    JOIN mdl_enrol e ON e.id = ue.enrolid AND e.courseid = xxx
    WHERE ue.userid = u.id
)
Russell England
  • 9,436
  • 1
  • 27
  • 41
  • Thanks Russell, but I am getting the error - Please do to include the table name prefix mdl_ in the SQL. Instead, put the un-prefixed table name inside {} characters.- – DKeeler Oct 13 '21 at 13:20
  • Looks like you are using a plugin in Moodle? Yeah, in Moodle code we never use the prefix, the table name should be surrounded by {} eg mdl_user should be {user}. But if you are using SQL directly via phpmyadmin for example, then you will need to specify the mdl_ prefix. – Russell England Oct 13 '21 at 17:26
1

Update... I found using "mdl_" will not work within the moodle framework, but you can replace it with "prefix_" and IT WORKS! Thank you SO much! I never would have thought of this setup.

SELECT u.id AS userid, u.firstname, u.lastname, u.email
FROM prefix_user u
WHERE u.deleted = 0
AND u.suspended = 0
and firstname not like "Guest user"

AND NOT EXISTS (
    SELECT ue.userid
    FROM prefix_user_enrolments ue
    JOIN prefix_enrol e ON e.id = ue.enrolid AND e.courseid = XXX
    WHERE ue.userid = u.id
)

ORDER BY
Lastname,
Firstname
DKeeler
  • 33
  • 3