Re: SELECT & recursion

"Mike Carroll" <mcarroll_at_pobox.com> wrote:
> If I'm using a procedural language, I can implement this by recursion:
> starting with the given object, search upwards for parents until there
> are no more. I don't see how to do this with a SELECT statement. Is
> there some SQL/SELECT construct that I'm overlooking?

It is called "WITH", and was first implemented in the SQL standard fashion in DB2. Most SQL implementations don't support it yet...

The notion is thus...

WITH recursive_tab (col1, col2, col3) as (select col1, col2, col3 from some_table root  where [conditions indicating "root" nodes]

 union all

 select children.col1, children.col2, children.col3  from some_table children, recursive_tab  where [conditions that connect children to elements already in  recursive_tab]
)
select * from recursive_tab;

Notice that in the second subquery, recursive_tab refers to itself; that's where the recursion takes place.

This is very much like the LET form found in Lisp and ML, and could presumably be used nonrecursively to short-hand initializations for queries...

Oracle has had a "CONNECT BY" structure to handle network/graph problems; it has never been standardized.

-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "_at_" ^ tld;;
http://linuxdatabases.info/info/slony.html
"...make -k all to compile  everything in the core distribution.  This
will take anywhere from 15 minutes  (on a Cray Y-MP) to 12 hours."  
-- X Window System Release Notes