Question

In which table(s) in my school's Canvas Data 2 Postgres database would I find a mapping of cross-listed courses linking SIS ID of each course to the SIS ID of the course cross-listed into?

From the Admin > Settings > Reports menu I can run a Provisioning report and select "X list CSV".  The generated report shows the cross-listed course's SIS ID and SIS ID of the course it's cross-listed into.  I would like to query the same data from CD2.

Thank you

KeithSmith_au · Accepted Answer

The question is a little confusing.  Courses are not cross listed - cross listing "moves" a section from one course to another.

If you look at the course_sections table for the section, it contains two canvas course identifiers - course_id is the unique canvas course identifier of the course the section is currently in.  If the section has been cross listed into that course, then the unique canvas course identifier of the course the section was originally created in is found in the nonxlist_course_id field.  (This may not be the most recent course the section was in if it has been cross listed multiple times, but it the course where the section was originally created).

To get what you want (sis_id of the courses, which they may not necessarily have). you need something along the lines of

select   a.sis_source_id as section_sis_id, a.name as section_name, a.id as canvas_section_id, b.sis_source_id as original_course_sis_id, b.id as original_canvas_course_id, c.sis_source_id as current_course_sis_id, c.id as current_canvas_course_id

from course_sections a

inner join courses  b on b.id = a.nonxlist_course_id

inner join courses c on c.id = a.course_id

where a.nonxlist_course_id is not null