Re: Help with Moodle Mysql query

[Peter Brawley]

Improving readability ...

SELECT 
  f.course AS 'Course ID', 
  c.fullname AS 'Course Name', 
  concat('<a target="_new" href="%%WWWROOT%%/mod/assign/view.php?id=',cm.id,'">',a.name,'</a>') 
    AS 'Assign name',
  cm.id AS 'Assign ID', 
  CASE 
    WHEN f.completion = 0 THEN "Do not indicate activity completion" 
    WHEN f.completion = 1 THEN "Students can manually mark" 
    WHEN f.completion = 2 THEN "Show activity as complete when conditions are" 
  END As 'Completion tracking' 
FROM prefix_assignment     AS a 
JOIN prefix_course         AS c  ON a.course=c.id 
JOIN prefix_course_modules AS cm ON cm.course=c.id AND cm.instance=f.id 
JOIN prefix_modules        AS m  ON cm.module=m.id AND m.name='assign'

The FROM clause has no table name or alias `f` for f.course_id, or for f.completion.