Re: join and trees

"grasp06110" <grasp06110_at_yahoo.com> a écrit dans le message de news: 1181176847.044879.247820_at_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
|

I don't understand why you don't want to use the subquery but you can use factoring clause (I didn't check it):

with tree as (

   select

      *
    from
      tree
    where
      tree.child != 'ROOT'
    start with
      tree.child = 'ROOT'
    connect by
      tree.parent = prior tree.child
  )
select * from tree, more_info mi
where
  tree.child = mi.child
/

Regards
Michel Cadot Received on Thu Jun 07 2007 - 00:03:34 CDT