SQL Query to find all possible paths

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);
inse开发者_运维技巧rt 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.

---

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

---

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

---

Try a subquery... inferring this from your initial post, hope it helps.

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

(I'm a MSSQL person so apologies if this doesn't map 100% to PL-SQL but you get the idea)

Edit: Sorry, I see you also want 4,101. Maybe two levels of subquery then?

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