join and trees

From: grasp06110 <grasp06110_at_yahoo.com>
Date: Wed, 06 Jun 2007 17:40:47 -0700
Message-ID: <1181176847.044879.247820@o5g2000hsb.googlegroups.com>

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 possible http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/9b9c705f88a41c54/d046958055d36826?lnk=gst&q=tree+join&rnum=1#d046958055d36826 */

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_thread/thread/1a532fc1255ee9e2/5d3bda09e73c941a#5d3bda09e73c941a

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