I need to put together a site level (cot course level) custom report that appears as follows:
Name, Profile_Field_1, Profile_Field_2, Profile_Field_3, Profile_Field_4, Profile_Field_5, Complete,
Jane Smith, a, b, c, d, e, Yes,
Where all Profile_Fields are custom user profile fields & 'Complete' is whether a student has completed all courses they're enrolled in. I'm finding the structure of Moodle's tables very confusing where it comes to profile fields - I've never worked with tables that are nested the way these are before. I'm not sure where to start on the course completion column.
My failing query so far:
CONCAT (u.firstname, u.lastname) Name,
IF (ROUND(g.finalgrade / g.rawgrademax * 100, 2) > 79, 'Yes', 'No') Complete
FROM mdl_user u
LEFT JOIN mdl_user_info_data d1 ON d1.userid = u.id
LEFT JOIN mdl_user_info_field f1 ON f1.id = d1.fieldid AND f1.shortname = "Profile_Field_1"
LEFT JOIN mdl_user_info_data d2 ON d2.userid = u.id
LEFT JOIN mdl_user_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = "Profile_Field_2"
LEFT JOIN mdl_user_info_data d3 ON d3.userid = u.id
LEFT JOIN mdl_user_info_field f3 ON d3.fieldid = f3.id AND f3.shortname = "Profile_Field_3"
LEFT JOIN mdl_user_info_data d4 ON d4.userid = u.id
LEFT JOIN mdl_user_info_field f4 ON d4.fieldid = f4.id AND f4.shortname = "Profile_Field_4"
LEFT JOIN mdl_user_info_data d5 ON d5.userid = u.id
LEFT JOIN mdl_user_info_field f5 ON d5.fieldid = f5.id AND f5.shortname = "Profile_Field_5"
LEFT JOIN mdl_user_info_data d6 ON d6.userid = u.id
LEFT JOIN mdl_user_info_field f6 ON d6.fieldid = f6.id AND f6.shortname = "Profile_Field_6"
JOIN mdl_grade_grades g ON g.userid = u.id
GROUP BY u.id
ORDER BY `Name` ASC
Any help would be very much appreciated. Thanks.