Originally posted by ligang
appreciate very much if you could provide some example data
You're missing out on the fun! If you insist, try the following - I do NOT suggest you create you tables like this, it's just for proof of principle:
Code:
create table line(
lineKey   integer,
lineName  varchar2(20)
);

create table line_seg(
lineKey  integer,
fromStop varchar2(1),
toStop   varchar2(1)
);

insert into line values(1,'First');
insert into line values(2,'Second');
insert into line values(3,'Circle');

insert into line_seg values(1,'A','B');
insert into line_seg values(1,'B','C');
insert into line_seg values(1,'C','D');
insert into line_seg values(1,'D','E');
insert into line_seg values(1,'E','F');

insert into line_seg values(2,'B','C');
insert into line_seg values(2,'C','E');

insert into line_seg values(3,'A','B');
insert into line_seg values(3,'B','C');
insert into line_seg values(3,'C','D');
insert into line_seg values(3,'D','A');


select lineName from line l
where 'D' IN
(select s.toStop from line_seg s
 connect by prior s.toStop = s.fromStop
 and              s.lineKey = l.linekey
 and              s.toStop <> 'B'
 start with s.fromStop = 'B'
 and        s.lineKey = l.lineKey);