LEFT or RIGHT JOIN - can't see where I'm going wrong?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

LEFT or RIGHT JOIN - can't see where I'm going wrong?

Pól Ua Laoínecháin
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...


Reply | Threaded
Open this post in threaded view
|

Re: LEFT or RIGHT JOIN - can't see where I'm going wrong?

David G Johnston
On Saturday, January 23, 2021, Pól Ua Laoínecháin <[hidden email]> 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.