I would love some help on writing a custom SQL query to return the date completed, the course, the user's state and the user's role.
The custom user fields are located in the mdl_user_info_data table.
Get the state you would SELECT data FROM mdl_user_info_data WHERE fieldid = 1
Get the role you would SELECT data FROM mdl_user_info_data WHERE fieldid = 2
SELECT cc.timecompleted AS datecompleted, cc.course AS id, cc.userid AS user, c.id AS courseid, c.shortname AS course, u.userid AS userinfoid,
(u.data FROM u WHERE u.fieldid = 1) AS state,
(u.data FROM u WHERE u.fieldid = 2) AS role
FROM mdl_course_completions cc JOIN mdl_course c ON (cc.course = c.id) JOIN mdl_user_info_data u ON (cc.userid = u.userid)
WHERE cc.timecompleted >= 0
Any help would be greatly appriciated.
Thanks,
Steven