Retrieve Roots with Nodes in a Hierarchy with PL/SQL

Question

I have a simple parent/child type view with two columns: MYID and MYPARENTID. In PL/SQL, getting a list of all of the children of a parent is simple enough:

SELECT MYID 
FROM MYVIEW 
START WITH MYID = 'TargetId1' 
CONNECT BY PRIOR MYID = MYPARENTID

And I would get something back like this:

MYID
-----------
TargetId1
TargetId1Child1
TargetId1Grandchild1

But now let's say that I want to do this for a set of parents, all at once:

SELECT MYID 
FROM MYVIEW 
START WITH MYID IN ('TargetId1', 'TargetId2', 'TargetId3') 
CONNECT BY PRIOR MYID = MYPARENTID

My result looks like this:

MYID
---------
TargetId1
TargetId1Child1
TargetId1Grandchild1
TargetId2
TargetId2Child2
TargetId2Grandchild1
TargetId3
TargetId3Child3
TargetId3Grandchild1

When I do it this way, I lose the ability to know where a particular child node came from. I get back a list of children, but I want to know which root (essentially, the START WITH value) each child originated from; I want a result set that looks like this:

MYID                    ROOT
----------------------------------
TargetId1               TargetId1
TargetId1Child1         TargetId1
TargetId1Grandchild1    TargetId1
TargetId2               TargetId2
TargetId2Child2         TargetId2
TargetId2Grandchild2    TargetId2
TargetId3               TargetId3
TargetId3Child3         TargetId3
TargetId3Grandchild3    TargetId3

How can I do this?

Answer 1

I don't know PL/SQL, but you might be able to:

• Insert the TargetId list into a temp table
• Join the temp table with the view

E.g.

CREATE TABLE #Parents (TargetId)

INSERT INTO #Parents TargetId1
INSERT INTO #Parents TargetId2
...
INSERT INTO #Parents TargetId10

SELECT MYID, #Parents.TargetID 
FROM MYVIEW, #Parents
CONNECT BY MYID = #Parents.TargetID
CONNECT BY PRIOR MYID = MYPARENTID

Answer 2

I have not used it myself yet, but from my understanding of your question, I would think that the operator CONNECT_BY_ROOT is what you are looking for. Requires Oracle 10g, though.

SELECT MYID
      ,CONNECT_BY_ROOT MYID ROOT
FROM MYVIEW 
START WITH MYID IN ('TargetId1', 'TargetId2', 'TargetId3') 
CONNECT BY PRIOR MYID = MYPARENTID