Hierarchical select/join problem

[sch...@starmedia.net]

I'm having some difficulty performing a select over a hierarchy to grab just a
particular branch of the tree.

Say I had the following two tables...

Msg
---
MessageID NUMBER PRIMARY KEY
Subject VARCHAR2(255)

Thread
------
MessageID NUMBER FOREIGN KEY (Msg.MessageID)
Parent NUMBER FOREIGN KEY (Msg.MessageID)

And I wished to grab the conversation thread spawned from message #1, I would
do the following...

select level, messageid, parent
from thread
start with MessageID = 1
connect by prior messageid = parent;

And the following is returned...

LEVEL MESSAGEID PARENT
---------- ---------- ----------
1 1
2 2 1
3 61 2
2 3 1
2 4 1
2 45 1
2 46 1
3 56 46
3 57 46
3 58 46
3 59 46
etc...

The problem is I wish to join this with information from the Msg table, while
preserving its order, and while still getting the LEVEL psuedo column.

I attempted...
select LEVEL, t.Parent, m.MessageID, m.Subject
from msg m, thread t
where exists (
select MessageID
from thread t
where t.MessageID = m.MessageID
start with t.MessageID = 1
connect by prior t.MessageID = t.Parent
) and t.MessageID = m.MessageID;

but two things go wrong there... 1, the hierarchy order is lost, it comes out
ordered by m.MessageID, not in hierarchy order. Also, the LEVEL psuedo column
is not passwd out of the inner select, so it comes out as 0...

LEVEL PARENT MESSAGEID SUBJECT
---------- ---------- ---------- ------------
0 1 Top level test post

0 1 2 First test reply to the top.

0 1 3 2nd test reply to the top
0 1 4 3rd test reply to the top
etc...

So I need to craft a statement to take a branch of the tree, join its results
with another table while still holding onto LEVEL and preserving the
hierarchical order of the branch. I can even use one that does not keep
LEVEL, just as long as it preserves the hierarch order since I can infer
LEVEL from the order and the parent.

One suggestion was to create a temp table of the branch, then join on that...

create table branch_tmp as
select MessageID, Parent from thread
start with MessageID = 1
connect by prior MessageID = Parent;
select m.MessageID, m.Subject, Parent
from msg m, branch_tmp b
where m.MessageID = b.branch_tmp;

But this has obvious performance and maintainability issues. It also does not
hold onto the LEVEL psuedo column (if you place LEVEL in the select, Oracle
attempts to create a column in the new table called 'LEVEL', which is a no-no,
since its a keyword).

I have yet to see this problem even touched upon in the Oracle literature.
(Book authors, hint hint :) Perhaps I'm attacking this problem from the wrong
angle? Being of the Perl mindset, I'm not used to have a language which
arbitrarily doesn't allow me to do certain things.

Thanks in advance for help.

--

Michael G Schwern Applications Engineer
(212)548-9643 Starmedia Networks Unix Bigot
sch...@starmedia.net Raving Perl Advocate
@h{@a[grep{!$_%2}0..$#a]}=@a[grep{$_%2}0..$#a];

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

[Kevin P. Fleming]

You're almost there; you do need to create a temp table to hold the
intermediate result, but you need to give the LEVEL pseudo-column a column
alias so it can have a usable column name in the new table. The other option
is to use this hierarchical query in a cursor in a PL/SQL stored procedure,
and do the join procedurally.

sch...@starmedia.net wrote in message <6qdh7j$7te$1...@nnrp1.dejanews.com>...
<snip>

[sch...@starmedia.net]

The Oracle programmer here and I seem to have come up with a good solution:

select t.depth, t.parent, m.MessageID, m.Subject
from msg m,
( select b.MessageID, b.Parent, LEVEL AS DEPTH
from thread b
start with b.MessageID = 1
connect by prior b.MessageID = b.Parent
) t
WHERE t.MessageID = m.MessageID;

Of course, it annoys me to no end that Oracle/SQL makes me have to jump
through those hoops, but hey, it works.

In article <p8sy1.7059$DI5.16...@news.rdc1.az.home.com>,

<snicker-snatch>