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