Analytics and reporting

Hi there,

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: