Oracle SQL: start with … connect by example: pruning branches

Create the table

create table prune_test (
  parent  number,
  child   number
);

Fill the data

insert into prune_test values (null,   1);
insert into prune_test values (null,   6);
insert into prune_test values (null,   7);

insert into prune_test values (   1,  12);
insert into prune_test values (   1,  14);
insert into prune_test values (   1,  15);

insert into prune_test values (   6,  61);
insert into prune_test values (   6,  63);
insert into prune_test values (   6,  65);
insert into prune_test values (   6,  69);

insert into prune_test values (   7,  71);
insert into prune_test values (   7,  74);

insert into prune_test values (  12, 120);
insert into prune_test values (  12, 124);
insert into prune_test values (  12, 127);

insert into prune_test values (  65, 653);

insert into prune_test values (  71, 712);
insert into prune_test values (  71, 713);
insert into prune_test values (  71, 715);

insert into prune_test values (  74, 744);
insert into prune_test values (  74, 746);
insert into prune_test values (  74, 748);

insert into prune_test values ( 712,7122);
insert into prune_test values ( 712,7125);
insert into prune_test values ( 712,7127);

insert into prune_test values ( 748,7481);
insert into prune_test values ( 748,7483);
insert into prune_test values ( 748,7487);

Select hierarchically and prune some nodes

select
  lpad(' ', 2*level) || child
from
  prune_test
start with
  parent is null
connect by
  prior child = parent and
  parent not in (1, 71)  -- Exclude children below and 71
; 

  1
  6
    61
    63
    65
      653
    69
  7
    71
    74
      744
      746
      748
	7481
	7483
	7487

Cleaning up

drop table prune_test purge;

See also