Unable to join quiz_submission_fact to quiz_submission_dim?

[rubyn]

Please let us know if we are joining incorrectly, or is this a bug?

I expect these to be able to join to one other.  E.g. if I want to get a list of all quiz submissions that have been done, I should be able to see both when the user had finished the quiz (dim) and their score (fact).

But this returns an empty set:

SELECT * from quiz_submission_fact qsf inner join quiz_submission_dim qsd on qsd.submission_id=qsf.quiz_submission_id;

Thinking I got the ID wrong, I also tried this, which is also an empty set:

SELECT * from quiz_submission_dim qsd inner join quiz_submission_fact qsf on qsd.id=qsf.quiz_submission_id;

[aallamsetty · Accepted solution]

Hi Ruby,

Your first query did not return any results because quiz_submission_dim.submission_id is a foreign key to submission_dim.id, as you can see from the following query.

canvas=# SELECT count(*) from quiz_submission_dim qsd INNER JOIN submission_dim sd ON sd.id = qsd.submission_id;

count

-------

66236

(1 row)

However, your second query is absolutely correct and the reason you didn't receive any results is due to a bug. Thank you for pointing it out. The schema of the quiz_submission_fact table would be changing and you should be able to see the correct schema for the table populated correctly by this time tomorrow.