1 Reply Latest reply: Feb 1, 2013 2:16 AM by 858519 RSS

    i want to help  connect by prior query

    858519
      table structure

      node_id parent_node_id
      =======================
      100
      101 100
      102 100
      1021 102
      1022 102
      10221 1022
      10222 1022

      above example tree like-

      100
      101
      102
      1021
           1022
           10221
           10222

      i want to output like(if input 1022)

      id id1
      ----------------------
      1022 100
      1022 102
      1022 1022
      1022 10221
      1022 10222

      create table node_temp(node_id number,parent_node_id number)

      insert into node_temp(node_id) values(100);
      insert into node_temp(node_id,parent_node_id) values(101,100);
      insert into node_temp(node_id,parent_node_id) values(102,100);
      insert into node_temp(node_id,parent_node_id) values(1021,102);
      insert into node_temp(node_id,parent_node_id) values(1022,102);
      insert into node_temp(node_id,parent_node_id) values(10221,1022);
      insert into node_temp(node_id,parent_node_id) values(10222,1022);

      Edited by: 855516 on Feb 1, 2013 1:46 PM
        • 1. Re: i want to help  connect by prior query
          IvanBlanarik
          Hi, try this:
          WITH t AS (
          SELECT 100 AS node_id, cast(NULL AS NUMBER) AS parent_node_id FROM dual UNION ALL
          SELECT 101 AS node_id, 100 AS parent_node_id FROM dual UNION ALL
          SELECT 102 AS node_id, 100 AS parent_node_id FROM dual UNION ALL
          SELECT 1021 AS node_id, 102 AS parent_node_id FROM dual UNION ALL
          SELECT 1022 AS node_id, 102 AS parent_node_id FROM dual UNION ALL
          SELECT 10221 AS node_id, 1022 AS parent_node_id FROM dual UNION ALL
          SELECT 10222 AS node_id, 1022 AS parent_node_id FROM dual
          )
          SELECT DISTINCT CONNECT_BY_ROOT parent_node_id AS ID, node_id AS id1
          FROM t
          CONNECT BY t.node_id = PRIOR parent_node_id
          START WITH parent_node_id = 1022
          ORDER BY 1, 2;