clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335795 fiddles created (27474 in the last week).

create table foo( id integer primary key , parent_id integer references foo , bar char(1) );
 hidden batch(es)


insert into foo(id,parent_id,bar) select 1,null,'A' from dual union all select 2,1,'A' from dual union all select 3,2,'B' from dual union all select 4,1,'B' from dual union all select 5,4,'B' from dual union all select 6,4,'A' from dual union all select 7,1,'B' from dual union all select 8,7,'A' from dual union all select 9,8,'A' from dual union all select 10,9,'B' from dual;
10 rows affected
 hidden batch(es)


select * from foo where bar='B';
ID PARENT_ID BAR
3 2 B
4 1 B
5 4 B
7 1 B
10 9 B
 hidden batch(es)


select * from foo where bar='B' start with parent_id is null connect by parent_id=(prior id) and (prior bar)<>'B';
ID PARENT_ID BAR
3 2 B
4 1 B
7 1 B
 hidden batch(es)