AnsweredAssumed Answered

SQL query that displays the term, course number, course name, assignment name, and score

Question asked by Glenn Saito on Jun 12, 2019
Latest reply on Jul 18, 2019 by Glenn Saito

Hello,

I'm learning the basics of Canvas Data and SQL and I'm trying to create a simple report that displays the term, course number, course name, assignment name, and score. I think I have the correct tables, but I'm not sure which columns (e.g., id, course_id, assignment_id) to join. The below query runs without syntax errors and I get a report, but when I log in to Canvas and access a course that's in the report, the information in the report is not correct. For example, when I'm in the course, I'm not able to find the assignment and score that's in the report. Any suggestions?

 

SELECT enrollment_term_dim.name, course_dim.code, course_dim.name, assignment_dim.title, submission_fact.score
FROM submission_fact
JOIN course_dim
ON course_dim.id=submission_fact.course_id
JOIN assignment_dim
ON assignment_dim.id=submission_fact.assignment_id
JOIN enrollment_term_dim
ON enrollment_term_dim.id=submission_fact.enrollment_term_id
;

Outcomes