Обсуждение: LEFT or RIGHT JOIN - can't see where I'm going wrong?
Hi all, I have a problem which *_should_* be very simple, but I'm stuck! I have 3 tables (DDL and DML shown below and in the fiddle here): https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b4e069919fc2052938196bcafe47a043 student, quiz and student_score student PK = (s_id), quiz (PK = q_id), and student_score is a JOINing table (Associative Entity) with a PK of (ss_s_id, ss_q_id). Now, I have 3 students: INSERT INTO student VALUES (12345678, 'Student1_name'), (40204123, 'Student2_name'), (40213894, 'Student3_name'); and three quizzes: INSERT INTO quiz (q_id, q_title) VALUES (1, 'Quiz 1'), (2, 'Quiz 2'), (3, 'Quiz 3'), (4, 'Quiz 4'); And for 1 student - no. 40204123, I have results for 3 quizzes: INSERT INTO student_score (ss_s_id, ss_q_id, points, ss_ts) VALUES (40204123, 1, 80, '2021-01-12 15:37:11'), (40204123, 2, 75, '2021-01-12 15:38:06'), (40204123, 3, 30, '2021-01-13 22:13:13'); Now, I have a query: SELECT q.*, ss.*, s.* FROM quiz q LEFT JOIN student_score ss ON q.q_id = ss.ss_q_id LEFT JOIN student s ON ss.ss_s_id = s.s_id WHERE s.s_id = 40204123 ORDER BY q.q_id; And the result is (hope alignement is OK - or see the fiddle): q_id q_titles s_s_id ss_q_id pointsss_ts s_id s_name 1 Quiz 1 40204123 1 80 2021-01-12 15:37:11 40204123 Student2_name 2 Quiz 2 40204123 2 75 2021-01-12 15:38:06 40204123 Student2_name 3 Quiz 3 40204123 3 30 2021-01-13 22:13:13 40204123 Student2_name What I want is a result for quiz 4 with NULL for the student_points fields but including the student_id and esp. the name - I'll put COALESCE in for the NULL grade for quiz 4. Fiddle: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b4e069919fc2052938196bcafe47a043 I'm beating my head off a wall here - I"m sure that it's quite easy - just one of those days... cabin-fever maybe... :-) If there's a better way of formulating the schema, I'm all ears - but I'd also like a solution - with an explanation just in case - I'll probably have a Homer <slaps forehead... "Doh"> moment, but just in case. If you require any further information, please don't hesitate to contact me on-list. TIA and rgs, Pól...
On Saturday, January 23, 2021, Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:
What I want is a result for quiz 4 with NULL for the student_points
fields but including the student_id and esp. the name - I'll put
COALESCE in for the NULL grade for quiz 4.
Cross join students and quizes so every combination is shown then left join the scores onto that combined relation.
David J.