Hi,
We have the following query that gets student status in courses. If user student is enrolled in a course, then is marked with a 'X', empty otherwise.
For every course I have to add a new subquery. I would like to know if there is a better way to do this. (We use this query in configurable reports plugin)
SELECT
user1.firstname AS Firstname,
user1.lastname AS Lastname,
user1.email AS Email,
if (exists( SELECT
ue.id
FROM mdl_user_enrolments AS ue
JOIN mdl_enrol AS en ON en.id = ue.enrolid
JOIN mdl_course AS course ON course.id = en.courseid
WHERE user1.id = ue.userid AND course.id = '1'), 'X', '') as "Enrolled Course One"
FROM mdl_user AS user1
LEFT JOIN mdl_user_enrolments AS ue ON ue.userid = user1.id
LEFT JOIN mdl_enrol AS en ON en.id = ue.enrolid
LEFT JOIN mdl_course AS course ON course.id = en.courseid
[...]
WHERE user1.deleted='0'
Thank you in advance!