Re: Recursive closure

From: Todd Gillespie <toddg_at_linux128.ma.utexas.edu>
Date: Sat, 21 Jul 2001 23:33:27 GMT
Message-ID: <9i0hfd$h7i$1@geraldo.cc.utexas.edu>

Tim Galle <38726_at_stud.ehsal.be> wrote:
: I have a table with a recursive relation. Something like:
: tblName(Number (PK), Name, SuperNumber (FK) )

: I would like to create a query who gives all the names at all the levels.
: (number differs from record to record) You can't do that in SQL2,
: but I heared it was possible in SQL3.

I don't know the SQL3 spec off the top of my head; but in Oracle you can write this with a CONNECT BY:

select Name, level from tblName
connect by SuperNumber = Number
start with Number = (select Number from tblName

where SuperNumber is NULL);

(where level is a psuedocolumn generated by the query; it changes according to where on the tree you start from)

DB2 also offers something similar with different syntax.

Joe Celko has some useful advice on faking it on other DBs; I think "SQL for Smarties" has it.

: How do you write this in relational algebra?

dunno. Ask Date.

: Does someone know where I could find this information?

HTH Received on Sat Jul 21 2001 - 18:33:27 CDT