Dear All. I wrote the query below to list all course users with their group.
BUT I've noticed that if a user is enrolled in several courses with a different group, that query could not print the correct group of the particular course on which I'm running the query. So, could you help me to understand the issue?
thank you
***
SELECT
u.lastname,
u.firstname,
u.email,
g.name,
DATE_FORMAT(FROM_UNIXTIME(ue.timecreated), '%d-%m-%Y'),
gi.itemname,
DATE_FORMAT(FROM_UNIXTIME(gg.timemodified),'%d-%m-%Y' ),
ROUND(gi.gradepass, 0) AS 'Points to Pass',
ROUND(gg.finalgrade, 0) AS 'Points',
ROUND(gg.finalgrade / gi.grademax * 100, 0) AS 'Final Grade %',
(CASE WHEN ROUND(gg.finalgrade, 0) >= gi.gradepass THEN 'Passed' ELSE 'Failed' END)
,DATE_FORMAT(FROM_UNIXTIME(ci.timecreated),'%d-%m-%Y')
FROM mdl_grade_grades AS gg
INNER JOIN mdl_grade_items AS gi ON gg.itemid = gi.id
INNER JOIN mdl_course AS c ON gi.courseid = c.id
INNER JOIN mdl_course_categories AS cc ON c.category = cc.id
INNER JOIN mdl_user AS u ON gg.userid = u.id
LEFT JOIN mdl_customcert_issues AS ci ON ci.userid = u.id
INNER JOIN mdl_groups_members AS gm ON u.id = gm.userid
INNER JOIN mdl_groups AS g ON gm.groupid = g.id
LEFT JOIN mdl_user_enrolments AS ue ON ue.userid = u.id
LEFT JOIN mdl_enrol AS e ON ue.enrolid = ue.userid
WHERE (gi.itemname IS NOT NULL) and c.id = 10
AND (gi.itemtype = 'mod' OR gi.itemtype = 'manual')
AND (gi.itemmodule = 'quiz' OR gi.itemmodule IS NULL)
AND (gg.timemodified IS NOT NULL)
#Group BY email
ORDER BY u.lastname, u.firstname