I'm trying to create a report which shows a list of all the people enrolled in a course, along with their groups and enrolment methods. Having multiple lines per user is fine. I just want to create a CSV file that I can then analyse further.
Here's what I've got so far. It's returning multiple lines per user, but isn't returning any lines where Group is Null.
SELECT Enrolments.CourseName, Enrolments.EnrolledUserID, Enrolments.UserEmail, Enrolments.UserName, Enrolments.UserCountry, g.name AS GroupName, Enrolments.enrolID, Enrolments.EnrolName, Enrolments.AutoEnrolName FROM (SELECT e.id AS EnrolID, e.name AS EnrolName, e.customchar2 AS AutoEnrolName, c1.id AS CourseID1, c1.shortname AS CourseName, u1.id AS EnrolledUserID, u1.email AS UserEmail, u1.username AS UserName, u1.country AS UserCountry, u1.confirmed AS UserConfirmed, u1.deleted AS UserDeleted FROM prefix_user AS u1 LEFT JOIN prefix_user_enrolments AS ue ON u1.id = ue.userid LEFT JOIN prefix_enrol AS e ON e.id = ue.enrolid LEFT JOIN prefix_course AS c1 ON e.courseid = c1.id WHERE c1.id = %%COURSEID%% AND u1.email NOT LIKE "%@xxx.org" AND e.enrol NOT LIKE "manual" ) AS Enrolments LEFT JOIN prefix_groups_members AS m ON Enrolments.EnrolledUserID = m.userid JOIN prefix_groups AS g ON m.groupid = g.id JOIN prefix_course AS c ON g.courseid = Enrolments.CourseID1
What I'd like to end up with is a table like this:
|John||Group 1||Self-enrol 1|
|John||Group 2||Self-enrol 1|