how to select all children and parents of a node in sql / oracle

Question

I have a table like below

MyTable

| ID     |  PARENT_ID |
-----------------------------
|   20      | null      |
|   40     | null      |
|   50     | null      |
|   70     | 122       |
|   100    | 40        |
|   102    | 4         |
|   126    | 100       |
|   9     | 50         |
|   122    | 40        |
|   123    | 9         |

I want to select hierarchy tree including all children and parent like below for the given three children 126 ,70 and 123

Expected output

| ID     |  PARENT_ID |
-----------------------------
|   126     | 100      |
|   100     | 40       |
|   40      | null     |
|   70      | 122      |
|   122     | 40       |
|   123     | 9        |
|   9       | 50       |
|   50      | null     |

I have tried

select ID, PARENT_ID
from MyTable
start with ID=126 //this for example 
connect by prior ID=Parent;

Please show us what you have attempted thus far, it would help not only us help you by modifying your query, but also better explaining what you want because this doesn't really make any sense.. — haag1, Apr 08 '19 at 12:51

score 3 · Accepted Answer · answered Apr 08 '19 at 12:57

You can do it with hierarchical query using CONNECT BY PRIOR clause:

select * from MyTable start with id in (126,70,123)
         connect by prior parent_id = id;

NOTE: Since you have two nodes with parent_id = 40, you will get two rows with 40 as ID and null as parent_id. If you want to leave only one row, use distinct clause:

select distinct * from MyTable start with id in (126,70,123)
         connect by prior parent_id = id;

how to select all children and parents of a node in sql / oracle

1 Answers1