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

Pól Ua Laoínecháin <linehanp(at)tcd(dot)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