Path: news.f.de.plusline.net!news-fra1.dfn.de!news.tele.dk!feed118.news.tele.dk!postnews.google.com!o5g2000hsb.googlegroups.com!not-for-mail
From:  grasp06110 <grasp06110@yahoo.com>
Newsgroups: comp.databases.oracle.misc
Subject: join and trees
Date: Wed, 06 Jun 2007 17:40:47 -0700
Organization: http://groups.google.com
Lines: 141
Message-ID: <1181176847.044879.247820@o5g2000hsb.googlegroups.com>
NNTP-Posting-Host: 75.21.54.206
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1181176847 28663 127.0.0.1 (7 Jun 2007 00:40:47 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 7 Jun 2007 00:40:47 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.12) Gecko/20070508 Firefox/1.5.0.12,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: o5g2000hsb.googlegroups.com; posting-host=75.21.54.206;
   posting-account=_inzqg0AAADDTAM-mX2WJqdM9jruqGED
Xref: news.f.de.plusline.net comp.databases.oracle.misc:79878

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

/* 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