If so, here's one way to get it:
`FOLDER_ID NAME TYPE ROOT ---------- -------------------- -------------------- ---------- 1 root1 root 1 4 folder1a node 1 7 folder1aa node 1 10 folder1aaa node 1 12 folder1aab node 1 14 folder1aac node 1 8 folder1ab node 1 11 folder1aba node 1 13 folder1abb node 1 15 folder1abc node 1 9 folder1ac node 1 5 folder1b node 1 6 folder1c node 1 2 root2 root 2 3 root3 root 3
Note that this is walking down the tree; we are finding the roots (in the START WITH clause), and then finding their descendants, but remembering where we started, so we can display it on each row. A Top-Down query like this is the most efficient way to get the output shown above. There are times when you want to do a Bottom-Up query, where you find the ancestors of nodes. This is typically done by reversing which column in the CONNECT BY clause gets the PRIOR operator. So if a top-down query uses:
SELECT f.* , CONNECT_BY_ROOT pc.child_id AS root FROM folders f JOIN parent_child pc ON pc.child_id = f.folder_id START WITH pc.parent_id = 0 CONNECT BY pc.parent_id = PRIOR pc.child_id ;
then a bottom-up query on the same table will probably use:
CONNECT BY pc.parent_id = PRIOR pc.child_id
Edited by: Frank Kulash on Nov 20, 2012 1:50 PM
CONNECT BY PRIOR pc.parent_id = pc.child_id
user12200443 wrote:If you change the constraint to "ON DELETE CASCADE", then all you have to do is DELETE the parent, and all the descendants will be DELETEd automatically.
Thanks, I will give this a try. The reason I want to get the leaf nodes (walk from bottom up) is that I have to delete the leaf nodes first before the parents because of integrity constraint violations. I have to delete the entry in the 'parent_child' table and then delete the node/folder itself.
WITH connect_by_results AS ( SELECT f.* , CONNECT_BY_ROOT pc.child_id AS root , ROWNUM AS r_num FROM folders f JOIN parent_child pc ON pc.child_id = f.folder_id START WITH pc.parent_id = 0 CONNECT BY pc.parent_id = PRIOR pc.child_id ) SELECT * FROM connect_by_results ORDER BY r_num DESC ;
user12200443 wrote:That's what the START WITH clause does.
I am not sure that I will be able to get this to work.
I need to pass an ID to the select statement that is the top most level id of the node from whence to traverse down (or up as would be the case).
I have a list of the root nodes that are orphaned and need to iterate through this list and put all nodes into a collection from bottom to top, then iterate through this list and delete in order. Not sure if this is possible to pass the starting / parent_id to the select statement?Sure. Unless you have a really old version of Oracle, you can use a sub-query in the START WITH clause.
... I think delete cascade only works on a row and all tables that have a foreign key pointing to it but would not traverse the tree.Why do you think that?
user12200443 wrote:No, ON DELETE CASCADE deletes rows, not tables. Even if all the rows from a table are deleted, the table remains.
a delete cascade, in my understanding will delete children tables that have a foreign key pointing to a parent.