pg.ddx.io pgsql-novice@postgresql.org mailing list archive
help / color / mirror / Atom feedLEFT or RIGHT JOIN - can't see where I'm going wrong?
3+ messages / 3 participants
[nested] [flat]
* LEFT or RIGHT JOIN - can't see where I'm going wrong?
@ 2021-01-23 08:23 Pól Ua Laoínecháin <linehanp@tcd.ie>
0 siblings, 2 replies; 3+ messages in thread
From: Pól Ua Laoínecháin @ 2021-01-23 08:23 UTC (permalink / raw)
To: pgsql-novice@lists.postgresql.org
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...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LEFT or RIGHT JOIN - can't see where I'm going wrong?
@ 2021-01-23 16:21 David G. Johnston <david.g.johnston@gmail.com>
parent: Pól Ua Laoínecháin <linehanp@tcd.ie>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2021-01-23 16:21 UTC (permalink / raw)
To: Pól Ua Laoínecháin <linehanp@tcd.ie>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LEFT or RIGHT JOIN - can't see where I'm going wrong?
@ 2021-03-07 18:15 Narendra Joshi <narendraj9@gmail.com>
parent: Pól Ua Laoínecháin <linehanp@tcd.ie>
1 sibling, 0 replies; 3+ messages in thread
From: Narendra Joshi @ 2021-03-07 18:15 UTC (permalink / raw)
To: Pól Ua Laoínecháin <linehanp@tcd.ie>; +Cc: pgsql-novice@lists.postgresql.org
Pól Ua Laoínecháin <linehanp@tcd.ie> writes:
> 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');
As long as there is at least on entry in `student_score` for a given
Quiz, there won't be any NULL values for the quiz row in the final query
that you have.
I think what you are trying to achieve can be done with a query like
THIS:
#v+
SELECT q.*, s.*, ss.*
FROM quiz q
CROSS JOIN student s
LEFT JOIN student_score ss
ON ss.ss_q_id = q.q_id AND ss.ss_s_id = s.s_id;
#v-
Best,
> 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...
>
--
Narendra Joshi
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-03-07 18:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-23 08:23 LEFT or RIGHT JOIN - can't see where I'm going wrong? Pól Ua Laoínecháin <linehanp@tcd.ie>
2021-01-23 16:21 ` David G. Johnston <david.g.johnston@gmail.com>
2021-03-07 18:15 ` Narendra Joshi <narendraj9@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox