Zoiets ??
SQL Code:
SELECT *
FROM Student s
LEFT JOIN Vak v ON 1=1
LEFT JOIN StudentVak sv ON sv.vakID=v.vakID AND sv.studentID=s.studentID
Resultaat bijvoorbeeld:
Code:
studentid naam vakid naam studentid vakid
1 1 Jan 1 Engels 1 1
2 1 Jan 2 Duits NULL NULL
3 1 Jan 3 Frans NULL NULL
4 2 Piet 1 Engels NULL NULL
5 2 Piet 2 Duits 2 2
6 2 Piet 3 Frans 2 3
Een werkend voorbeeld kun je hier vinden
Ik haat de tabellen met " eromheen dus heb ze hier even weggehaald.
http://rextester.com/COYOT78642
SQL Code:
CREATE TABLE Student (
studentID int4 NOT NULL,
naam varchar(20),
PRIMARY KEY(studentID)
);
CREATE TABLE StudentVak (
studentID int4 NOT NULL,
vakID int4 NOT NULL,
PRIMARY KEY(studentID,vakID)
);
CREATE TABLE Vak (
vakID int4 NOT NULL,
naam varchar(20),
PRIMARY KEY(vakID)
);
ALTER TABLE StudentVak ADD CONSTRAINT Ref_StudentVak_to_Student FOREIGN KEY (studentID)
REFERENCES Student(studentID)
MATCH SIMPLE
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;
ALTER TABLE StudentVak ADD CONSTRAINT Ref_StudentVak_to_Vak FOREIGN KEY (vakID)
REFERENCES Vak(vakID)
MATCH SIMPLE
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;
INSERT INTO Student VALUES (1,'Jan');
INSERT INTO Student VALUES (2,'Piet');
INSERT INTO Vak VALUES (1,'Engels');
INSERT INTO Vak VALUES (2,'Duits');
INSERT INTO Vak VALUES (3,'Frans');
INSERT INTO StudentVak VALUES (1,1);
INSERT INTO StudentVak VALUES (2,2);
INSERT INTO StudentVak VALUES (2,3);
SELECT *
FROM Student s
LEFT JOIN Vak v ON 1=1
LEFT JOIN StudentVak sv ON sv.vakID=v.vakID AND sv.studentID=s.studentID
Bookmarks