[es] - SQL upit. Gde gresim?

LightBow
London

Član broj: 4829
Poruke: 156
*.bmly.cable.ntl.com.

Profil

^{08.05.2007. u 20:56}

Pozdrav.
napravio sam bazu koja cuva podatke o studentima, osoblju fakulteta, ocenama itd. Problematican query treba da izlista ocene studenta za module (predmete) koje je polagao. Tabele su:
student (SudentID, StudentName,...)
module (ModuleID, ModuleName,....)
studentMarks(StudentID, ModuleID, Mark)

Query:

Code:
select m.ModuleID, m.ModuleName, sm.Mark from module m, studentMarks sm
       where m.ModuleID in
          ( select ModuleID from studentMarks
            where StudentID = 1 AND Mark is not null);

Medjutim, umesto tri reda, ja dobijem 720, redova. Kada izvrsim samo subquery, dobijem tri. Broj redova u studentMarks tabeli je 240, dakle ja dobijam cisti dekartov proizvod.
Da li bi neko mogao ovo da objasni, jer ja ne vidim gde je greska u logici ovog queryja.
Hvala

^{08.05.2007. u 20:56}

mb_sa

Član broj: 50529
Poruke: 157
*.PPPoE-4082.sa.bih.net.ba.

Profil

Re: SQL upit. Gde gresim?

^{08.05.2007. u 21:20}

Uopce nije potraban takav pristup, dovoljno je:

Code:
SELECT m.ModuleID, m.ModuleName, sm.Mark
FROM module m, studentMarks sm
WHERE m.ModuleID = sm.ModuleID
AND sm.StudentID =1 AND Mark is not null
GROUP BY sm.ModuleID

^{08.05.2007. u 21:20}

Miloš Baić

Član broj: 72468
Poruke: 1123
*.dialup.neobee.net.

Sajt: www.2advanced.com

Profil

Re: SQL upit. Gde gresim?

^{08.05.2007. u 23:06}

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

If you go out of your mind, do it quietly, so as not to disturb those around you...

Prikačeni fajlovi

Marks.PNG - 4.11k

^{08.05.2007. u 23:06}

LightBow
London

Član broj: 4829
Poruke: 156
84.13.228.*

Profil

Re: SQL upit. Gde gresim?

^{09.05.2007. u 00:38}

Hvala, ovi vasi upiti naravno resavaju problem. Samo mi nije jasno zasto ne moze koristeci IN, jer sudeci prema silnim tutorijalima koje sam procitao trebalo bi da radi.
Izgleda da je ipak praksa najvaznija.

^{09.05.2007. u 00:38}

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 511
213.244.228.*

Sajt: www.baze-podataka.net

Profil

Re: SQL upit. Gde gresim?

^{09.05.2007. u 09:26}

Nije problem u IN, vec je problem u Cartesian join-u:

Code:

from module m, studentMarks sm

"The best code is no code at all." - Zidar
"biggest obstacle to learning SQL is unlearning procedural programming" - Joe Celko

^{09.05.2007. u 09:26}