4.12.2001
rekurzia rulez!!!
mame tabulku clen
vyselektujme cloveka a jeho potomkov
ALTER TABLE RN_CLEN DROP FOREIGN KEY RN_NAIDOTEC

UPDATE rn_clen SET idotec=0 WHERE id=1

referencia sa neda znova nahodit...

WITH rn_potomkovia (id, idpotomok) AS
(SELECT koren.idotec, koren.id
 FROM rn_clen koren
 WHERE koren.idotec is not null
 UNION ALL
 SELECT otec.id, dieta.id
 FROM rn_potomkovia otec, rn_clen dieta
 WHERE otec.idpotomok=dieta.idotec
)
SELECT * FROM rn_potomkovia
ORDER BY 1,2

uroven vnarania
WITH rn_potomkovia (id, idpotomok, uroven) AS
(SELECT koren.idotec, koren.id, 1
 FROM rn_clen koren
 WHERE koren.idotec is not null
 UNION ALL
 SELECT otec.id, dieta.id, uroven+1
 FROM rn_potomkovia otec, rn_clen dieta
 WHERE otec.idpotomok=dieta.idotec
)
SELECT * FROM rn_potomkovia
ORDER BY 1,2

WITH rn_potomkovia (id, idpotomok, uroven) AS
(SELECT koren.idotec, koren.id, 1
 FROM rn_clen koren
 WHERE koren.idotec is not null
 UNION ALL
 SELECT otec.id, dieta.id, uroven+1
 FROM rn_potomkovia otec, rn_clen dieta
 WHERE otec.idpotomok=dieta.idotec
)
SELECT * FROM rn_potomkovia
ORDER BY 1,2

este lepsie

WITH rn_potomkovia (id, idpotomok, uroven) AS
(SELECT koren.idotec, koren.id, 1
 FROM rn_clen koren
 WHERE koren.idotec is not null
 UNION ALL
 SELECT otec.id, dieta.id, uroven+1
 FROM rn_potomkovia otec, rn_clen dieta
 WHERE otec.idpotomok=dieta.idotec AND uroven<2
)
SELECT * FROM rn_potomkovia
ORDER BY 1,2

rn_predmet
ID
skratka
nazov

rn_popis_predmetu
IDPREDMET
IDTYPTEXTU
TEXT

rn_typ_textu
ID
popis
1.anotacia
2.literatura
--------------------

CREATE TABLE rn_predmet2(
  id integer not null,
  skratka VARCHAR(10), 
  nazov VARCHAR(20),
  kredit integer,
  PRIMARY KEY(id)
);

CREATE TABLE rn_popis_predmetu(
  idpredmet integer not null,
  idtyptextu integer not null,
  idtext VARCHAR(20),
  PRIMARY KEY(idpredmet, idtyptextu)
);

CREATE TABLE rn_typ_textu(
  id integer not null,
  popis CHAR,
  PRIMARY KEY(id)
);

ALTER TABLE rn_popis_predmetu
ADD CONSTRAINT rn_naid
FOREIGN KEY (id) REFERENCES rn_predmet2

ALTER TABLE rn_popis_predmetu ADD CONSTRAINT rn_naid FOREIGN KEY (idpredmet) REFERENCES rn_predmet2

ALTER TABLE rn_popis_predmetu ADD CONSTRAINT rn_natyptextuid FOREIGN KEY (idtyptextu) REFERENCES rn_typ_textu


nie kazdy predmet ma aj anotaciu aj literaturu
vyberte predmety a ich prisluchajuce anotacie a literaturu
vyberte prve 2 predmety s najvacsim kreditom usporiadane podla
skratky