Correlating the assignments and quizzes CD2 tables

[pgo586]

I've read carefully two threads that discuss the contents of the quizzes and assignment tables in CD2: 

• https://community.canvaslms.com/t5/Data-and-Analytics-Group/Assignments-vs-Quizzes/m-p/576490#M1836
• https://community.canvaslms.com/t5/Data-and-Analytics-Group/New-Quizzes-CD2/m-p/604408#M2791I 

I was able to aggregate a few important facts out of those posts, mainly:

• Graded quizzes, surveys and discussions also show up in the Assignments table, but not their ungraded types.
• New quizzes do appear in the assignments table nut not in the Quizzes table
• The content_tags and context_external_tools tables can be used to see which Assignment records correspond to New Quizzes. 

However, I am still unsure as to how to associate records in the Assignments table to the Quizzes table (or vice versa). In other words, since my goal is to get a listing that includes *all of the assignments and quizzes in a course* for an instructor to see in a visualization dashboard, if I retrieve records from both tables in order to achieve this goal, I need to be able to tell which pair of them refer to the same entity (so I don't duplicate them in my listing). Can anybody offer some tip on how to do this efficiently? (note that I don't need to tell what type of quiz or assignment they are; I only need to be able to list them all, without duplication). 

Update: I wonder if one way to do this would be to simply retrieve all the assignment records for the course from the Assignments table, and then add to it the list of 'ungraded' quiz items, presumably retrieved from the Quizzes table. If so, how can I tell whether a particular Quizzes table record represents an ungraded type? (I can see that the value of the quiz_type field in our database is either 'assignment' or 'graded_survey', so not sure what I'd use). 

[ldavenport4015]

Hello @pgo586 , 

Would you be able to use a SQL statement FULL JOIN (or UNION of right and left) with the assignments and quizzes table to create a new table?  Are you familiar with this entity relationship diagram for joining tables in Canvas? In your case assignments.id = quizzes.assignment_id. I am no expert and I use ai to a lot to set me up and then work through the details of filters and columns I want in the resulting table. 

It sounds like you want a resulting table that:

• Includes all rows from assignments filtered on course_id
• Includes all rows from quizzes filtered on course_id 
• Combines matching rows where assignments.id = quizzes.assignment_id.
• Shows NULL values for columns from the table that doesn't have a match.
• Has no duplicate rows (if a row from assignments.id matches a row from quizzes.assignment_id, it should appear as one combined row, not two separate rows).

[pgo586]

Looks like somehow I missed the Quizzes.assignments_id field ....which kind of obviates my question completely (funny, I have worked with CD2 data for at least 2 years now but never noticed this field as I didn't need to do any explicit correlation between them 😞 ). Yes, I am familiar with sql queries, but I appreciate your detailed explanation above anyway.  After looking at this field, I cannot find null values in that assignment_id column, suggesting to me that ALL quizzes have a corresponding assignment record. I had assumed that ungraded quizzes potentially did *not* have such an associated assignment record, but perhaps it was an incorrect interpretation of the other threads. If so, then I am wondering whether the most efficient thing to do here is to simply obtain ALL the (active, published) assignments in the Assignments table, since these will subsume the quizzes. Is this a correct interpretation though? 

[pgo586]

For the record, I wanted to explain here what I ended up finding as the most desirable method (also taking into account the fact that I'm not using SQL for these queries, but the Splunk query language - so I'm not retrieving from tables but searching via Splunk indices): I was definitely wrong above in assuming there were no NULL 'assignment_id' field in Quizzes, so I ended up using what I considered in my original posting's update: retrieving all Assignment records for the course (from canvas.assignments) and also all the Quizzes records for the course (from canvas.quizzes) that do NOT have a value in the 'assignment_id' field (note: this mechanism makes it un-necessary to deduplicate or correlate these records, which makes it more efficient). As far as my testing goes, this appears to be working fine for now, but I'd be interested in hearing from @ldavenport4015 or others that may think my method could potentially miss some quizzes. Specifically, can I be safe in assuming that all the ungraded quizzes -from the point of view of what an instructor sees-  are represented in the Quizzes table, or not?
BTW, if somebody using Splunk to store Canvas data is interested in the specific query I'm using, feel free to reach out (I'm happy to share).