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 come up with SQL query that would return the following result when where clause is this : "c2=100"

result set:

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

The reason result set contains "3,101" is because it's reachable through "3,100". And same for "4,101" : reachable through -> "3,101" -> "3,100".

UPDATE: This table contains identifiers from 2 different data sets after similarity join. So the idea is to allow user to search by any identifier and show all possible matches between two datasets. That is why when user searches for "c2=100" I also want to show "3,101" and "4,101" to show full graph of matches.

Thanks.

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

Same idea as jonearles answer, but using recursive subquery factoring:

  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