I'm using the code below to pull the user's location, name, course, email, position, and completion date.
When I run the code, I get duplicate rows for a number of employees.
Furthermore, I'm looking for a way to limit the number of courses the query returns and I'm uncertain if it's better to use course id or name?
I cannot get either to work.
USING Moodle 3.5
Any and all help is appreciated. Thanks.
SELECT uid2.data AS store, u.lastname, u.firstname, u.email, uid.data AS position,
CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname)
ELSE gi.itemname
END AS 'Course',
ROUND(gg.finalgrade) AS Grade,
FROM_UNIXTIME(gg.timemodified, '%M %D, %Y') AS 'Last Taken'
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
INNER JOIN prefix_user_info_data AS uid ON uid.userid = u.id
INNER JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id
INNER JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
INNER JOIN prefix_user_info_field AS uif2 ON uid2.fieldid = uif2.id
%%FILTER_COURSES:c.id%%
%%FILTER_USERS:uid2.data%%
WHERE 1=1 AND uif.shortname = 'position' AND uif2.shortname = 'store' AND gg.finalgrade > 79
ORDER BY uid2.data AND lastname