Bilo bi lepo da si umesto svojih metapodataka dao metapodatke u standardnom SQL
zapisu kao:
Code:
CREATE TABLE tabela1 (
id1 INTEGER,
naziv1 VARCHAR(20),
datum1 DATE,
id2 INTEGER);
CREATE TABLE tabela2 (
id2 INTEGER,
naziv2 VARCHAR(20),
datum2 DATE);
INSERT INTO tabela1 (id1, naziv1, datum1, id2) VALUES (1, 'naz-1', '03.12.2006', 3);
INSERT INTO tabela1 (id1, naziv1, datum1, id2) VALUES (2, 'naz-2', '08.09.2006', 3);
INSERT INTO tabela1 (id1, naziv1, datum1, id2) VALUES (3, 'naz-3', '17.10.2006', 5);
INSERT INTO tabela1 (id1, naziv1, datum1, id2) VALUES (4, 'naz-4', '20.12.2006', 5);
INSERT INTO tabela2 (id2, naziv2, datum2) VALUES (3, 'ime-3.1', '01.09.2006');
INSERT INTO tabela2 (id2, naziv2, datum2) VALUES (3, 'ime-3.2', '01.11.2006');
INSERT INTO tabela2 (id2, naziv2, datum2) VALUES (3, 'ime-3.3', '15.12.2006');
INSERT INTO tabela2 (id2, naziv2, datum2) VALUES (5, 'ime-5.1', '01.06.2006');
INSERT INTO tabela2 (id2, naziv2, datum2) VALUES (5, 'ime-5.2', '13.12.2006');
To bi ustedelo vreme ljudima koji su voljni da ti pomognu.
Resenje je:
Code:
SELECT t12.id1, t12.naziv1, t12.datum1, t12.id2, t3.naziv2, t12.datum2
FROM (SELECT t1.id1, t1.naziv1, t1.datum1, t1.id2, MAX(t2.datum2) AS datum2
FROM tabela1 AS t1
INNER JOIN
tabela2 AS t2
ON t2.id2 = t1.id2
AND t2.datum2 <= t1.datum1
GROUP BY t1.id1, t1.naziv1, t1.datum1, t1.id2
HAVING MAX(t2.datum2) <= t1.datum1
) AS t12
INNER JOIN
tabela2 AS t3
ON t12.id2 = t3.id2
AND t12.datum2 = t3.datum2
ORDER BY t12.id1
"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo