I have the following SQL query in my configurable report
SELECT CONCAT(u.firstname , ' ' , u.lastname) AS 'User',
u.city AS 'City',
c.fullname AS 'Course',
gi.itemname AS 'Test',
SEC_TO_TIME(qa.timefinish - qa.timestart) AS 'Time',
CONCAT(ROUND(gg.finalgrade,2), '/', ROUND(gg.rawgrademax,2)) AS 'Grade',
ROUND(gg.finalgrade/gg.rawgrademax*100,0) AS 'Grade in %'
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = "quiz"
LEFT JOIN mdl_grade_grades AS gg ON gg.userid = u.id AND gg.itemid = gi.id
LEFT JOIN mdl_quiz AS q ON q.course = c.id AND q.name = gi.itemname
LEFT JOIN (SELECT userid, timestart, timefinish, MAX(attempt) AS maxattempt FROM (SELECT * FROM mdl_quiz_attempts WHERE quiz = q.id AND userid = u.id) AS mqa) AS qa ON qa.userid = u.id AND qa.userid = u.id
WHERE 1 %%FILTER_COURSES:c.id%%
ORDER BY lastname
It works fine except last JOIN that should return only last user's attempt on quiz. The reason in that SQL can't see nested aliases in subquery
WHERE quiz = q.id AND userid = u.id
Is there any way to solve that problem?