General developer forum

Useful SQL Queries?

Picture of Bonnie Mioduchoski
Re: Useful SQL Queries?

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: 

user2.firstname AS Firstname,
user2.lastname AS Lastname, 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 =
JOIN prefix_user_enrolments AS ue ON ue.enrolid =
JOIN prefix_user AS user2 ON ue.userid =

I appreciate any help you can give. Thanks!!

Average of ratings: -