SQL Query to find all possible paths

Question

I have this table:

create table testtb (c1 number, c2 number);
insert into testtb values (1, 100);
insert into testtb values (2, 100);
insert into testtb values (3, 100);
insert into testtb values (3, 101);
insert into testtb values (4, 101);
insert into testtb values (5, 102);
commit; 

I'm struggling to find an SQL query that returns the following result when the where clause is: "c2 = 100"

result set:

c1 c2
-- ---
1  100
2  100
3  100
3  101
4  101

As a result, the result set contains "3,101" because it is available through "3,100". And the same goes for "4,101": reachable value → "3,101" → "3,100".

UPDATE: . , . , "c2 = 100", "3,101" "4,101", .

.

Answer 1

select distinct c1, c2
from testtb
connect by nocycle prior c1 = c1 or prior c2 = c2
start with c2 = 100
order by c1, c2;

Answer 2

, jonearles, :

  WITH pathtb(c1,c2) AS
  (
  SELECT c1,c2 FROM testtb WHERE c2=100
  UNION ALL
  SELECT testtb.c1,testtb.c2 FROM
     testtb JOIN pathtb ON (pathtb.c1=testtb.c1 or pathtb.c2=testtb.c2)
  ) CYCLE c1,c2 set cycle TO 1 default 0
  SELECT DISTINCT c1,c2 FROM pathtb WHERE cycle=0
  ORDER BY c1,c2

Answer 3

... , , .

select * from testtbl where c1 in (select c1 from testtbl where c2=100)

( MSSQL, , 100% PL-SQL, )

: , , 4,101. , ?

    select *
    from testtbl
    where c2 in
    (select c2 from testtbl where c1 in (select c1 from testtbl where c2=100))