SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Subjects (`ID` INTEGER, `Subject` VARCHAR(8));
INSERT INTO Subjects (`ID`, `Subject`) VALUES
  ('1', 'Business'),
  ('2', 'IT'),
  ('3', 'Sports');
CREATE TABLE Participants (`ID` INTEGER, `Name` VARCHAR(8), `semester` INTEGER);
INSERT INTO Participants (`ID`, `Name`, `semester`) VALUES
  ('1', 'Meyer', '6'),
  ('2', 'Smith', '4'),
  ('3', 'Brown', '4'),
  ('4', 'White', '2'),
  ('5', 'Anthonie', '2'),
  ('6', 'Frankson', '2');
CREATE TABLE ParticipantsList (`Subject_ID` INTEGER, `Participant_ID` INTEGER);
INSERT INTO ParticipantsList (`Subject_ID`, `Participant_ID`) VALUES
  ('1', '2'), ('1', '3'), ('1', '5'),
  ('2', '4'), ('2', '6'),
  ('3', '1');
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;


ID
Name
Subject


1
Meyer
Sports

2
Smith
Business

3
Brown
Business

4
White
IT

5
Anthonie
Business

6
Frankson
IT


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


ID
Name
Subject


1
Meyer
Sports

2
Smith
Business

3
Brown
Business

4
White
IT

5
Anthonie
Business

6
Frankson
IT

ID	Name	Subject
1	Meyer	Sports
2	Smith	Business
3	Brown	Business
4	White	IT
5	Anthonie	Business
6	Frankson	IT