How can i solve blank spots in a SQL View?-CodePudding

In my sqllite-excercises i have discovered the following problem: I basically have three different Tables:

Subjects

PRIMARY KEY(ID)	Subject
1	Business
2	IT
3	Sports

Participants

PRIMARY KEY(ID)	Name	semester
1	Meyer	6
2	Smith	4
3	Brown	4
4	White	2
5	Anthonie	2
6	Frankson	2

They are referenced in the Table participants List

SUBJECT.ID	Participant.ID
1	2
1	3
1	5
2	4
2	6
3	1

Now im supposted to create a VIEW that contains: The Participants.ID, Participants.Name and Subjects.Subject so i have a Table that shows the ID, the Name and the Subject the participant is visiting.

So far I did this:

CREATE VIEW[OVERVIEW]AS
   SELECT Participants.ID,
          Participants.Name,
          Subjects.Subject
         from Participants
         LEFT JOIN Subjects on Participants.ID = Subjects.ID;

As a result i get this:

Participants.ID	Participant.Name	Subjects.Subject
1	Meyer	Business
2	Smith	IT
3	Brown	Sports
4	White	None
5	Anthonie	None
6	Frankson	None

And it makes sense since there are only three Subjects and i Leftjoined 6 Participants.ID on only 3 Subjects.Subject

How can i fill out the blank Subjects? So that the subjects for 4-6 are shown aswell? I hope you can understand my problem and i declared it good enough.

CodePudding user response：

You can't join Participants to Subjects because they are not directly related, so this ON clause:

on Participants.ID = Subjects.ID

does not make sense because the IDs of participants are not related to the IDs of the subjects.

You have the table ParticipantsList that can be used as an intermediate link between the other 2 tables:

SELECT p.ID, p.Name, s.Subject
FROM Participants p
LEFT JOIN ParticipantsList pl ON pl.Participant_ID = p.ID
LEFT JOIN Subjects s ON s.ID = pl.Subject_ID;

This will return all participants, even if they are not linked to any subject.

If you want only the participants for which there is at least 1 subject in the table ParticipantsList then you can use INNER joins.

For the sample data that you provide in your question in both cases the results are the same.

See the demo.