Re: Tree (forest) design

"Neo" <neo55592_at_hotmail.com> wrote in message news:4b45d3ad.0403031652.24caf1bb_at_posting.google.com...
> > > PS. I am still awaiting your solution to the problem posted in the
> > > "object algebra" thread.
> >
> > 0. Create hierarchy table with any known SQL-based method.
> > 1. Select path to the root from node A ("Ancestors list" query).
> > 2. Select path to the root from B.
> > 3. Intersect 1 and 2.
> > 4. Find the node most distant from the root in the result.
>
> Another person (in this thread) also claimed he could do it it with a
> one-line query. Why does it take you 4 additional steps? Since neither
> actually produced a report I am not sure who to believe.
>
> Can you prove that your steps actually work by producing a "Nearest
> Common Ancestor Report" equivalent to that shown at
> www.xdb1.com/Example/Ex076.asp starting from the same normalized,
> null-less data? Can you implement your steps using MS Access or
> SQL-Server so that we can verify it?

with jointAncestors as (
  select ename, empno, mgr from emp e
  connect by prior mgr = empno
  start with ename = 'SMITH'
  intersect
  select ename, empno, mgr from emp e
  connect by prior mgr = empno
  start with ename = 'ADAMS'
) select ename from jointAncestors j0
  where not exists (select 0 from jointAncestors j1 where j0.empno=j1.mgr)

returns 'JONES'

I used example emp table from oracle sample schema, but this could obviously be done with other hierachical methods and/or the other table. Received on Thu Mar 04 2004 - 02:52:52 CET