Re: Recursive closure
Date: Sat, 21 Jul 2001 23:33:27 GMT
Message-ID: <9i0hfd$h7i$1_at_geraldo.cc.utexas.edu>
Tim Galle <38726_at_stud.ehsal.be> wrote:
: I would like to create a query who gives all the names at all the levels.
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
: I have a table with a recursive relation. Something like:
: tblName(Number (PK), Name, SuperNumber (FK) )
: (number differs from record to record) You can't do that in SQL2,
: but I heared it was possible in SQL3.
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 Sun Jul 22 2001 - 01:33:27 CEST