Možda nešto ovako:
Code:
-- PostgreSQL 8.2
-- Zbog jednostavnosti bez kljuceva
create table student(
StudentID integer,
StudentName varchar(20)
);
create table module(
ModuleID integer,
ModuleName varchar(20)
);
create table studentsMarks(
StudentID integer,
ModuleID integer,
Marks varchar(20)
);
INSERT INTO student (StudentID, StudentName) VALUES (1, 'John Smit');
INSERT INTO student (StudentID, StudentName) VALUES (2, 'Abraham Lincoln');
INSERT INTO module (ModuleID, ModuleName) VALUES (1, 'History');
INSERT INTO module (ModuleID, ModuleName) VALUES (2, 'Geography');
INSERT INTO studentsMarks (StudentID, ModuleID, Marks) VALUES (1, 1, 8);
INSERT INTO studentsMarks (StudentID, ModuleID, Marks) VALUES (1, 2, 10);
INSERT INTO studentsMarks (StudentID, ModuleID, Marks) VALUES (2, 1, 7);
INSERT INTO studentsMarks (StudentID, ModuleID, Marks) VALUES (2, 2, 9);
--ako su evidentirani samo polozeni ispiti
--uslov da sm.Marks ne moze biti null
SELECT s.StudentName, m.ModuleName, sm.Marks
FROM ((studentsMarks sm
LEFT JOIN student s ON sm.StudentID = s.StudentID)
LEFT JOIN module m ON sm.ModuleID = m.ModuleID)
GROUP BY s.StudentName, m.ModuleName, sm.Marks
--uslov da sm.Marks moze biti null
SELECT s.StudentName, m.ModuleName, sm.Marks
FROM ((studentsMarks sm
LEFT JOIN student s ON sm.StudentID = s.StudentID)
LEFT JOIN module m ON sm.ModuleID = m.ModuleID)
WHERE sm.Marks is not null
GROUP BY s.StudentName, m.ModuleName, sm.Marks
Someone's sitting in the shade today because someone planted a tree a long time ago.