Thread: Recursive select

Recursive select

From
"Martin Smetak"
Date:
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]




Re: Recursive select

From
Ron Peterson
Date:
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


Recursive select

From
Daniel Wickstrom
Date:
>>>>> "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