SQL Report - list of all courses with teachers

SQL Report - list of all courses with teachers

by Michael Kowalski -
Number of replies: 2

Hi,

this script gives us list of courses with teachers. I want to add to this report courses, where numer of teachers is 0. Does anyone know, how to do this?


SELECT c.id, c.fullname, c.shortname, ra.userid, u.firstname, u.lastname

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

WHERE ra.roleid =3 

AND ctx.instanceid = c.id

Average of ratings: -
In reply to Michael Kowalski

Re: SQL Report - list of all courses with teachers

by Randy Thornton -
Picture of Documentation writers
There's a report that shows how to do this in the Ad-hoc reports page: https://docs.moodle.org/311/en/ad-hoc_contributed_reports#Courses_without_Teachers
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: SQL Report - list of all courses with teachers

by Michael Kowalski -
Yes, I have already done the second report, but I would like to have it all in one, complete list of courses. Row like this: course name; teacher name/teachers names/empty field (if there are no teachers on the course).
Average of ratings: -