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?
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