Hi Dale,
Thanks for sharing all of these queries. I wonder if you can help me? I'd like to use the configurable reports block to see users (first name, last name & email) and all the courses they're currently enrolled in. So far I can only see one of their courses. I'm doing this so I can see if anyone has been enrolled into two courses of the same class accidentally. I tried to duplicate the course part but I am not knowledgeable in mySQL. Here's what I have:
SELECT
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
course.fullname AS Course
,(SELECT shortname FROM prefix_role WHERE id=en.roleid) AS ROLE
,(SELECT name FROM prefix_role WHERE id=en.roleid) AS RoleName
FROM prefix_course AS course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user AS user2 ON ue.userid = user2.id
I appreciate any help you can give. Thanks!!