Обсуждение: Recursive select
Hi all! Anyone know if it's possible to make a recursive select from a table ? My problem: I got a table of "some categories" which all points to its parrent one(tree)...shown below. And I want to select all names of parrent categories of one child, lets say "fast[4]". Now I'm solving that with many SQL queries like : "SELECT main_id FROM cat WHERE id=4;"....but I would like to optimize this. Anyone can help or point me to a way ?? Thanks a lot,Minca Table : CAT ID | Main_id | Name ========================= 1 0 Car 2 1 Crash 3 1 Wash 4 3 Fast 5 1 Second_hand 6 0 House 7 3 Slow ....etc *(root)[0] -Car[1] -Crash[2] -Wash[3] -Fast[4] -Slow[7] -Second hand[5] -House[6]
Don't drive yourself crazy ( like me ;). You'll have to write some procedural code - sorry. I believe IBM's DB/2 supports recursive queries as defined by SQL3. Oracle provide a couple of non SQL standard clauses (CONNECT BY, LEVELS) to provide similar funcionality. -Ron- GPG and other info at: http://www.yellowbank.com/ Martin Smetak wrote: > > Hi all! > > Anyone know if it's possible to make a recursive select from a table ? > My problem: I got a table of "some categories" which all points to its > parrent one(tree)...shown below. And I want to select all names of > parrent categories of one child, lets say "fast[4]". Now I'm solving that > with > many SQL queries like : "SELECT main_id FROM cat WHERE id=4;"....but I would > like to optimize this. > > Anyone can help or point me to a way ?? > > Thanks a lot,Minca > > Table : CAT > ID | Main_id | Name > ========================= > 1 0 Car > 2 1 Crash > 3 1 Wash > 4 3 Fast > 5 1 Second_hand > 6 0 House > 7 3 Slow > ....etc > > *(root)[0] > -Car[1] > -Crash[2] > -Wash[3] > -Fast[4] > -Slow[7] > -Second hand[5] > -House[6] > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
>>>>> "Martin" == Martin Smetak <minca_sql@no9.cz> writes: Martin> Hi all! Anyone know if it's possible to make a recursive Martin> select from a table ? My problem: I got atable of "some Martin> categories" which all points to its parrent Martin> one(tree)...shown below. And I want to selectall names of Martin> parrent categories of one child, lets say "fast[4]". Now Martin> I'm solving that with manySQL queries like : "SELECT Martin> main_id FROM cat WHERE id=4;"....but I would like to Martin> optimize this. Martin> Anyone can help or point me to a way ?? The Openacs project has implemented a couple of different methods for handling tree queries. Check out these two threads: http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0000eC&topic_id=11&topic=OpenACS http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0000j6&topic_id=12&topic=OpenACS%204%2e0%20Design Regards, Dan Wickstrom