SQL ad-hoc report of ALL users not completed a course

Question

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??

Answer 1

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
)

Answer 2

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