rekurzivni sql upit

Imam dve tabele:
Table1: Table2:
Tab1ID (pk) 1 --------->∞ Tab1ID (pk)
Tab2x (pk)

Table1:
1
2
3
5
6
9

Table2:
1 2
1 3
1 4
2 5
2 6
3 7
3 8
5 9
5 10
6 11
6 12
9 13
9 14

tabele su u relaciji preko polja tab1id, pri cemu postoji ogranicenje (da bi se izbegla zatvorena petlja)
da se tab2x ne moze naci u paru sa tab1ID gde je tab2x <= tab1ID.

treba mi requrzivni upit za vrednost 1 tabele 1 koji bi dao sledecii rezultat:

1 2
1 3
1 4
1 2 2 5
1 2 2 6
1 3 3 7
1 3 3 8
1 2 2 5 5 9
1 2 2 5 5 10
1 2 2 6 6 11
1 2 2 6 6 12
1 2 2 5 5 9 9 13
1 2 2 5 5 9 9 14

sledecim sql izrazom dobijam neki rezultat
SELECT Table1.Tab1ID,Table2.Tab2x,
Table1_1.Tab1ID, Table2_1.Tab2x,
Table1_2.Tab1ID, Table2_2.Tab2x,
Table1_3.Tab1ID, Table2_3.Tab2x
FROM ((((((Table1 INNER JOIN Table2 ON Table1.Tab1ID = Table2.Tab1ID)
LEFT JOIN Table1 AS Table1_1 ON Table2.Tab2x = Table1_1.Tab1ID)
LEFT JOIN Table2 AS Table2_1 ON Table1_1.Tab1ID = Table2_1.Tab1ID)
LEFT JOIN Table1 AS Table1_2 ON Table2_1.Tab2x = Table1_2.Tab1ID)
LEFT JOIN Table2 AS Table2_2 ON Table1_2.Tab1ID = Table2_2.Tab1ID)
LEFT JOIN Table1 AS Table1_3 ON Table2_2.Tab2x = Table1_3.Tab1ID)
LEFT JOIN Table2 AS Table2_3 ON Table1_3.Tab1ID = Table2_3.Tab1ID
WHERE Table1.Tab1ID=1

rezultat koji se dobije je sledeci:

1 2 2 5 5 9 9 13
1 2 2 5 5 9 9 14
1 2 2 5 5 10
1 2 2 6 6 11
1 2 2 6 6 12
1 3 3 7
1 3 3 8
1 4

ovde postoji jedan otezavajuci moment, u sql izrazu moguce je napisati odredjeni broj JOIN-a odnosno moguce je postaviti n-ti broj rekurzija.

ima li neko ideju o rekurziji sa nepoznatim brojem iteracija, stim da je taj broj iteracija ipak konacan.

Ima resenje, Common Table Expressions ako si na verzioj 2005+, ili korisnicke funkcije koje vracaju tabelu, ako si na verziji 2000.

Ne umem to da napisem napamet, strpi se nekoliko sati, daj mi vremena da pripremim odgovor i dobices resenje.

Ukoliko ti je dostupna knjiga "Inside Microsoft SQL Server 2005 T-SQL Querying", autori Itzik Ben-Gan,Lubor Kollar, Dejan Sarka; mozes tamo da nadjes resenje za tvoj problem. Dejan Sarka je mislim iz Slovenije poreklom. Knjigu izdao Microsoft Press, moZe se naci na Amazon, kosta oko U4 50.00 ili i manje.

Nije losa ni starija knjiga, "Advanced Transact-SQL for SQL Server 2000" , Itzik Ben-Gan i Tom Moreau.

Jos bolja je nova, "Inside Microsoft SQL Server 2008 T-SQL Querying"

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Ne sumnjam, nisam znao da je izasla knjiga. Itzik ne ponavlja uglavnom stvari iz knjige u knjigu, pa ne bih bio iznendajen da knjiga za 2008 u stvari samo produzava gde je knjiga 2005 stala.Tako je bar bilo izmedju 2000 i 2005. U svakom slucaju, hvala na informaciji.

Zakacio sam dve skripte. Prva, Hijerarhija.sql kreira tabelu s kojom se kasnije radi. Tabela sadrzi zaposlene u firmi, gde postoji relacija na samu sebe. Svaki zaposleni (employee) ima nadredjenog managera (koji je i sam employee i ima svog menaqdzera). Svi su u istoj tabeli, i postoji FK sa tabele na samu sebe, uglavnom ono sto svi manje vise znamo. Tu se za prosecnog coveka znanje o hijerarhijama zavrsava - kako ih pamtimo u tabeli.

Itzik u knjizi objasnjava kako se pisu kveriji koji odgovaraju na pitanja o hijerarhijama. To se posytize pomocu funkcija. Lepa stvar je sto cak i ako se ne razume sve (a ako nemas knjigu, tesko ces razumeti), funkcije mogu da se iskoriste i preprave da sluze nasim konkretnim prilikama. Funkcije i SQL izrazi koji ih ilustruju su prilicno lepo objasnjeni komentarima, pa s enadam da ce ti pomoci.

U svakom slucaju, ja imam knjigu, pa ako zapnes, pitaj, a ja cu da pogledam u knjigu

Kod mene na production serveru rde dve funkcije koje sam prepisao od Itzika, kad ih nisam ni malo razumeo. Kasnije sam uspeo da prevedem nesto i u Access, znaci nesto sam kao razumeo. pomioci cemo koliko mozemo.


Prva skripta, Hijerarhija.sql , ima tri funkcije:

1) dbo.fn_subordinates1 - nija bas ono sto ti treba, ali je jako vazna za razumevanje pretrazivanja hijerarhije. Funkcija pokazuje svu decu izabranog roditelja.

2) dbo.fn_managers - inverzan zadatak, zadato je dete, pokazuje sve pretke

3) dbo.fn_subordinates3 - ono sto tebi treba. funkcija pokazuje svu decu izbranog roditelja u formatu 'jedn roditelj jedan red, deca kao comma delimited string u istom redu'

Za sve funkcije dati su i kveriji koji ilustruju sta se desava. Funkcije rade u verzijai 2000 i novijim. Uz svaku funkciju dato je i resenje sa rekurzivnim CTE. Ako imas iskustva sa CTE i rekurzijama, mozes da ih pretvoris u funkcije, krace su nego klasicne verzije. Klasicne verzije su medjutim lakse za razumevanje.

Druga skripta, Hijerarhija_Sorting.sql, sadrzi funkciju koja se bavi sortiranjem izlaza. Funkcija dbo.fn_subordinates1 vraca svu decu, ali se ne garantuje redosled medju decom. Na primer, ako roditelj broj 1 ima decu ciji su brojevi 2,3,5 deca se mogu pojaviti na izlazu u redosledu 2,2,5 ili bilo kom (i pojavljuju se) i nemoguce ih je sortirati. Ako se zeli sortirani izlaz, onda Itzik daje stored proceduru umesto funkcije, koja vraca sortirani set. Zanimljivo, a moze biti i korisno.

Srecan rad

Pa ista prica i sad, dodate novotarije iz SQL2008. Ako je trazis, sad ti je vreme za nabavku, kad je izasla drzali su bas jaku cenu a sad su pojeftinile za 60, 70% pred novu godinu a kako se gde snadjes... Izvinjavam se za mali off.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Da mu ga damo jos malo po off-topicu...:) Pored SQL 2008 features u novoj knjizi ima i prilicno matematike. Naime, dodata su i poglavlja o skupovima i predikatskoj logici, relacionoj algrebri i slozenosti algoritama. Takodje Lubor Kollar je napisao poglavlje o upitima nad particionisanim tabelama.

Za one koji sebe ne smatraju naprednim developerima, a voleli bi da to postanu, odlican izbor predstavlja takodje Itzik-ova knjiga "Inside Microsoft SQL Server 2008: T-SQL Programming".

Pozz,
M.

Potpis

Sad šta je tu je. A možda će da dođe i ova tvoja tetka iz Bosanske Krupe.

Ok,
veliko vam hvala na odgovorima i primerima,
sve knjige mogu se naci u pdf, ako nekome trebaju .................

odgovori su dosta dosta jasni i postoji nekoliko resenja

1. preko temp tabele
2. preko rekurzivnih poziva funkcije
3. preko CTE

ali ipak postoji ogranicenje na 32-e iteracije, u realnom svetu verovatno i sasvim dovoljno.

Hvala na pomoci.

Gde?

Ne razumem ovo:
U CTE mozes da postavis MAXRECURSION = 0 i onda nemas ogranicenje. Resenja sa CTE funkcijama uzmu ulaznu tabelu i vrete se koilko puta treba i na kraju izbace data set. Resenja sa UDF koje vracaju tabelu isto nemaju ogranicenja - vrte se dok ima podataka. Ispravi me ako gresim. Pokusacu da napravim primer sa vise od 32 nivoa hijerarhije, da proverimo.

Evo demonstracija da broj nivoa nije ogranice.

prvo, rekreiramo tabelu Employees i unesemo 50 employees, svaki je poslovodja onom sledecem. Unesmo 50 radnika, to nam daje 50 nivoa hijerarhije.


Pozivamo funkcije koje vec imamo:

Svaki red pokazuej radnika i sve nejgove rukovodioce.
Deo slike koja se dobije izgleda ovako:

Cela slika ide do nivoa 50, sto je vece od 32 :-)

Slika je mozda lepsa ovako:


To smo dobili ovim kverijam (ima ga u originalnoj skripti):



Znaci, nema ogranicenja po nivoima. Barem do 50, u praksi je 7-8 verovatno i previse.
:-)

Knjige sam skinuo, ja ih sada imam i mogu da ih posaljem mailom, linkove normalno
kao i svaki drugi IT nisam sacuvao, a da ih stavljamo negde mogu nas tuziti
za pirateriju. Sve zahteve za knjigama saljite e-mail na koji da ih dostavim.

Sto umesto svih tih maltretiranja ne koristite jednostavno hierarchyid tip? Ima malo vise posla pri insertu (ali ni to ako se odradi kako treba) a implicitno dobijate hijerarhiju i ispomoc pri pretrazivanju iste..

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Tacno, medjutim sve zavisi od verzije SQL servera. 2008 je prva MS SQL verzija koja ima hierarchyid tip. Ja se nadam da se sve ove stvari zaista mogu raditi sa hierarchyid tip i da ne moramo da cekamo verziju 2010...

neam 2008 pa ne mogu nista da kazem :-(