From: | Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie> |
---|---|
To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | LEFT or RIGHT JOIN - can't see where I'm going wrong? |
Date: | 2021-01-23 08:23:16 |
Message-ID: | CAF4RT5TYfCTEW8kLFjUqfUw_XXeAhudnmFL59kRGg9VDKEw-jQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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...
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-01-23 16:21:14 | Re: LEFT or RIGHT JOIN - can't see where I'm going wrong? |
Previous Message | Tom Lane | 2021-01-22 17:14:10 | Re: FDW |