For example,
SELECT u.id, c.id
FROM mdl_user u
INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id
INNER JOIN mdl_enrol e ON e.id = ue.enrolid
INNER JOIN mdl_course c ON e.courseid = c.id
and
SELECT u.id, c.id
FROM mdl_user u
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid
INNER JOIN mdl_course c ON c.id = ct.instanceid
INNER JOIN mdl_role r ON r.id = ra.roleid
WHERE r.id = 5
These 2 give me the same set of results
See the orginal answer from "james mergenthaler" : https://moodle.org/mod/forum/discuss.php?d=202383
When students are enrolled in a course, they are enrolled in the role of students in the context of a course. When users are assinged course creation rights for all the english courses, they are assigned the role of course_creator, in the context of a category. Etc. That rol_assignments table is kinda the core for queries about people in the system. "roles in context". That mdl_uers_enrollments table does not have a direct relationship with the course table. The mdl_enrol tables contains information about the type of enrollment a course is using. Someday moodle may use FK (foreign keys) which will make the relationships more clear, IMHO.
A query like this to your question:
SELECT
c.id AS courseid,
c.fullname,
u.username,
u.firstname,
u.lastname,
u.email
FROM
mdl_role_assignments ra
JOIN mdl_user u ON u.id = ra.userid
JOIN mdl_role r ON r.id = ra.roleid
JOIN mdl_context cxt ON cxt.id = ra.contextid
JOIN mdl_course c ON c.id = cxt.instanceid
WHERE ra.userid = u.id
AND ra.contextid = cxt.id
AND cxt.contextlevel =50
AND cxt.instanceid = c.id
AND roleid = 5
ORDER BY c.fullname
Se your answer based on your Moodle version here: https://docs.moodle.org/26/en/ad-hoc_contributed_reports#LIST_of_all_site_USERS_by_COURSE_enrollment_.28Moodle_2.x.29
