Having a node at level N

Question

I want to write the code by Having a node at level N (for example 3) of the tree, find all members up to level M (for example 6).How can I write this code? what is the best idea for this problem? I am new in oracle , please explain me and help me for resolve this problem .

Just as you would normally recurse a tree, but limit it to 3 levels of recursion/connection. *(Don't treat it as starting at level N, your start point is always Level 0, then you stop at Level 6-3, which is 3)* https://stackoverflow.com/a/50125911/53341 — MatBailie, Jan 28 '23 at 11:00
@MatBailie thanks for replying but I can not write this code , please help me — Big Dream American, Jan 28 '23 at 11:10

score 0 · Answer 1 · answered Jan 28 '23 at 11:23

CREATE TABLE tree (
    node_id     INT,
    parent_id   INT
)

INSERT INTO
  tree
          SELECT    1, NULL FROM DUAL
UNION ALL SELECT    2,    1 FROM DUAL
UNION ALL SELECT    3,    1 FROM DUAL
UNION ALL SELECT    4,    2 FROM DUAL
UNION ALL SELECT    5,    2 FROM DUAL
UNION ALL SELECT    6,    3 FROM DUAL
UNION ALL SELECT    7,    3 FROM DUAL
UNION ALL SELECT    8,    4 FROM DUAL
UNION ALL SELECT    9,    4 FROM DUAL
UNION ALL SELECT   10,    5 FROM DUAL
UNION ALL SELECT   11,    5 FROM DUAL
UNION ALL SELECT   12,    6 FROM DUAL
UNION ALL SELECT   13,    6 FROM DUAL
UNION ALL SELECT   14,    7 FROM DUAL
UNION ALL SELECT   15,    7 FROM DUAL

15 rows affected

SELECT
  node_id, parent_id, level
FROM
  tree
WHERE
  level < 3
CONNECT BY
  PRIOR node_id = parent_id
START WITH
  parent_id = 3
ORDER BY
  level, node_id

NODE_ID	PARENT_ID	LEVEL
6	3	1
7	3	1
12	6	2
13	6	2
14	7	2
15	7	2

fiddle

Having a node at level N

1 Answers1