Обсуждение: 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...

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.