From: Narendra Joshi <narendraj9@gmail.com>
To: =?utf-8?Q?P=C3=B3l?= Ua =?utf-8?B?TGFvw61uZWNow6Fpbg==?=
 <linehanp@tcd.ie>
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: LEFT or RIGHT JOIN - can't see where I'm going wrong?
References: 
 <CAF4RT5TYfCTEW8kLFjUqfUw_XXeAhudnmFL59kRGg9VDKEw-jQ@mail.gmail.com>
Date: Sun, 07 Mar 2021 19:15:18 +0100
In-Reply-To: 
 <CAF4RT5TYfCTEW8kLFjUqfUw_XXeAhudnmFL59kRGg9VDKEw-jQ@mail.gmail.com>
 (=?utf-8?Q?=22P=C3=B3l?=
	Ua =?utf-8?Q?Lao=C3=ADnech=C3=A1in=22's?= message of "Sat, 23 Jan 2021
 08:23:16 +0000")
Message-ID: <87im623k5l.fsf@gmail.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/28.0.50 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/87im623k5l.fsf%40gmail.com>
Precedence: bulk

P=C3=B3l Ua Lao=C3=ADnech=C3=A1in <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=3Dpostgres_12&fiddle=3Db4e069919fc2052938196bc=
afe47a043
>
> student, quiz and student_score
>
> student PK =3D (s_id), quiz (PK =3D 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:=20

#v+
SELECT q.*, s.*, ss.*
FROM quiz q
CROSS JOIN student s
LEFT JOIN student_score ss
  ON ss.ss_q_id =3D q.q_id AND ss.ss_s_id =3D 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 =3D ss.ss_q_id
> LEFT JOIN student s
>   ON ss.ss_s_id =3D s.s_id
> WHERE s.s_id =3D 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=3Dpostgres_12&fiddle=3Db4e069919fc2052938196bc=
afe47a043
>
> 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=C3=B3l...
>

--=20
Narendra Joshi