General developer forum

Useful SQL Queries?

 
Picture of Ruthine Burton
Re: Useful SQL Queries?
 

I managed to write the query to find pdfs, but would welcome any comments on better ways of writing this. It may also be useful to anyone else who gets a request like this.

The code:

SELECT

    prefix_files.filename AS PDF,

    prefix_course.fullname AS Course,

CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=', prefix_course.id,'">link</a>') AS 'Link To Course',

   prefix_course_modules.course     AS 'Course id'

FROM prefix_files

INNER JOIN prefix_context 

ON prefix_files.contextid = prefix_context.id

INNER JOIN prefix_course_modules

ON prefix_context.instanceid = prefix_course_modules.id

INNER JOIN prefix_course

ON prefix_course_modules.course = prefix_course.id

WHERE (prefix_files.filename LIKE '%pdf%')

GROUP BY prefix_course.id

 
Average of ratings: -