courses with zero reosurces

courses with zero reosurces

by m question -
Number of replies: 1
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



Average of ratings: -
In reply to m question

Re: courses with zero reosurces

by Peter Bowen -

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


Average of ratings: Useful (1)