Please explain me the query attached

Question

Folks,

Can someone please help me to explain the below query?

 SELECT   SOME_ID ROOT_CAT,
          CHILD_ID SOME_ID
 FROM    CHILD_TABLE
 START WITH SOME_ID IN (SELECT ROOT_ID FROM ROOT_TABLE)
 CONNECT BY NOCYCLE SOME_ID = PRIOR CHILD_ID;

Answer 1

This is used for fetching hierarchical data which is something like Parent - Child - Child of child etc.

start with -- this identifies all LEVEL=1 nodes in the tree
connect by -- describes how to walk from the parent nodes above to their children and their childrens children.

The general syntax is

select ... 
start with initial-condition 
connect by nocycle 
recurse-condition

The connect by construct establishes the recursion. All records that are part of the next lower hierarchical level are found by having SOME_ID= CHILD_ID.

CHILD_ID is a value found in the current hierarchical level

Your Query can be analogous to:

 SELECT   SOME_ID ROOT_CAT,
          CHILD_ID SOME_ID
 FROM    CHILD_TABLE
 START WITH SOME_ID IN (SELECT ROOT_ID FROM ROOT_TABLE)
 CONNECT BY NOCYCLE SOME_ID = PRIOR CHILD_ID;

 SELECT  PARENT_ID  CURRENT_PARENT,
         CHILD_ID   PARENT_ID_CURRENT_CHILD
 FROM LIST_OF_CHILDREN
 START WITH PARENT_ID IN ( SELECT SOME_RANDOM_PARENT_ID FROM LIST_OF_PARENTS)
 CONNECT BY NOCYCLE  PARENT_ID = PRIOR CHILD_ID