Hello,
what sql for creating report to show courses with no resources in Moodle.
i found this query to show number of resource
SELECT COUNT(l.id) COUNT, l.course, c.fullname coursename
FROM prefix_resource l INNER JOIN prefix_course c ON l.course = c.id
WHERE c.category IN (12,9,17)
GROUP BY course
ORDER BY COUNT DESC
but it does not show courses with zero count.
I read using outer join or left join but when I try it either give syntax error
Debug info: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'OUTER JOIN mdl_resource l ON l.course = c.id
thanks in advance for any help
You are starting with the resources and adding what course they belong to.
You need to start with the courses, and look for those that do not have resources. In addition, the GROUP BY course seems to only shows those with resources. The below will show you what you wish.
SELECT COUNT(l.id) COUNT, l.course, c.fullname coursename
FROM prefix_resource l INNER JOIN prefix_course c ON l.course = c.id
WHERE c.category IN (12,9,17)
GROUP BY course
ORDER BY COUNT DESC
Alternatively, the below will list just the courses which have no resources.
SELECT c.id, c.fullname coursename
FROM prefix_course c LEFT JOIN prefix_resource l ON c.id=l.course
WHERE l.course IS NULL
Cheers
Peter