Hi all, thanks for reading this thread.
I'm working on the following database query (using Ad-hoc database query plugin), and this is what I have now:
-------------
SELECT u.id AS 'user ID', concat(u.firstname, ' ',u.lastname) AS name, c.id AS 'Course_ID', c.fullname AS 'Course', g.id as 'Group ID', g.name as 'Group_Name', u.email AS 'Email address', da.fieldid AS 'Field ID', da.data AS 'Job_title/Employer/Primary',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS MAX, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage,
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') AS Pass
FROM prefix_course AS c
JOIN prefix_groups AS g ON g.courseid = c.id
JOIN prefix_groups_members AS m ON g.id = m.groupid
JOIN prefix_user AS u ON m.userid = u.id
JOIN prefix_user_info_data AS da ON da.userid = u.id
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON (gi.courseid = c.id AND gi.id = gg.itemid)
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE c.id = 101 AND gi.itemname = 'Final Assessment'
ORDER BY u.id ASC
---------------
As you can see the current query will generate a list of students (and which group they belong to) in a specific course (course id 101), and list out who passed (or not pass) the final exam. However, it seems that the query will NOT include students who didn't try the final exam (with no assessment score). Is there a way to fix this issue so that it would also display those students who did not take the final exam (but are enrolled in the same course)? Thanks for your help!
Judy