Hi all,
I have the below query which returns all users enrolled in a date range.
It works fine except for the fact it returns the role column as 'student' for everyone.
I just cant seem to wrap my head around this, I just need it to return what the role is for the enrolled user.
We only use two roles and users are enrolled in each course with only one role student or assessor
Any chance anyone knows where I am going wrong here?
Cheers
Rivv
------------------------------------------
SELECT
FROM_UNIXTIME(u.timecreated, '%d-%m-%Y') AS Login_Issued,
u.firstname as 'First_Name',
u.lastname as 'Last_Name',
u.email as 'Email',
u.institution as 'Institution',
(SELECT GROUP_CONCAT(DISTINCT course.fullname SEPARATOR ', ')
FROM {user_enrolments} ue
LEFT JOIN {enrol} en ON ue.enrolid = en.id
LEFT JOIN {course} course ON en.courseid = course.id
WHERE ue.userid = u.id
) as 'Course',
(SELECT GROUP_CONCAT( DISTINCT r.shortname SEPARATOR ', ')
FROM {user_enrolments} ue
LEFT JOIN {enrol} en ON ue.enrolid = en.id
LEFT JOIN {role} r ON en.roleid = r.id
) as 'Role'
FROM {user} u
WHERE u.timecreated >= :start_date AND u.timecreated <=:end_date
AND u.deleted = '0'
---------------------------------------------------------
