Re: join and trees

On Jun 6, 8:40 pm, grasp06110 <grasp06..._at_yahoo.com> wrote:
> Hi Everybody,
>
> Having trouble getting a join from a tree to work. I'm trying to get
> all of the child records for a node and join in data from another
> table with no luck.
>
> I would have thought that this would have done it but it returns 0
> records.
>
> /* 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
>
> /* this returns 3 records */
> 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
>
> /*
> this works but I would prefer to avoid the subquery if possiblehttp://groups.google.com/group/comp.databases.oracle.server/browse_th...
> */
>
> select
> *
> from
> (
> select
> *
> from
> tree
> where
> tree.child != 'ROOT'
> start with
> tree.child = 'ROOT'
> connect by
> tree.parent = prior tree.child
> ) tree,
> more_info mi
> where
> tree.child = mi.child
>
> Any help would be greatly appreciated.
>
> Thanks,
> John
>
> p.s. I posted earlier on a similar topic. Thanks for the help from
> Michel Cadot!http://groups.google.com/group/comp.databases.oracle.misc/browse_thre...
>
> DDL FOR EXAMPLE
>
> create table tree (
> parent varchar2(10),
> child varchar2(10),
> constraint tree_pk primary key(child),
> constraint tree_child_fk foreign key(parent) references tree(child)
> );
>
> insert into tree values (null, 'ROOT');
> insert into tree values ('ROOT', 'NODE1');
> insert into tree values ('ROOT', 'NODE2');
> insert into tree values ('ROOT', 'NODE3');
>
> create table more_info (
> more_info_id number,
> message varchar2(20),
> child varchar2(10),
> constraint more_info_pk primary key(more_info_id),
> constraint more_info_fk_child foreign key(child) references
> tree(child)
> );
>
> insert into more_info values (
> 1,
> 'Hi, I''m node 1',
> 'NODE1'
> );
>
> insert into more_info values (
> 2,
> 'Hi, I''m node 2',
> 'NODE2'
> );
>
> insert into more_info values (
> 3,
> 'Hi, I''m node 3',
> 'NODE3'
> );
>
> /* this returns 3 records */
> 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
>
> /* 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

Sorry, forgot to mention I'm currently looking at Oracle 9i.

Thanks again,
John Received on Wed Jun 06 2007 - 19:44:14 CDT