Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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.deReceived on Thu Jun 07 2007 - 05:23:54 CDT