Re: join and trees

grasp06110 schrieb:
> /* this returns no records found */
> select
> *
> from
> tree,
> more_info
> where
> tree.child != 'ROOT'
> and tree.child = more_info.child
> start with
> tree.child = 'ROOT'
> connect by
> tree.parent = prior tree.child

Hi,

the problem is that there is no corresponding row in more_info regarding your start node in tree. So just make an outer join and it works select

   *
from

   tree,
   more_info
where

   tree.child != 'ROOT'
   and tree.child = more_info.child (+)
start with

   tree.child = 'ROOT'
connect by

   tree.parent = prior tree.child;

or
select

   *
from

   tree
   left outer join more_info on tree.child = more_info.child where

   tree.child != 'ROOT'
start with

   tree.child = 'ROOT'
connect by

   tree.parent = prior tree.child;

HTH
Thomas

-- 
For answers by personal mail use: thomas.sommerfeld at domain ust-gmbh.de