with t1 as (
select 'A' head union all
select 'B'      union all
select 'C'
),   t2 as (
select 'A' head, '1' val union all
select 'A'     , '2'     union all
select 'B'     , '1'     union all
select 'B'     , '4'     union all
select 'B'     , '3'     union all
select 'C'     , '6'     union all
select 'C'     , '2'     union all
select 'C'     , '8'
)
select c.*
  from t2 a
     , t1 b
     , t2 c
 where a.val  = '1'
   and a.head = b.head
   and b.head = c.head

셀프조인인가요? 감사합니다. 늘 건강하십시오 ^^

이건 좀 그런가요..^^;

select *
  from TABLE2
where HEAD IN (select HEAD from TABLE2 where value = '1')

답을 다 찾으셨는데... 자세히 못보신 모양이네요...

from 절에 TABLE1 => TABLE2로 바꾸면 됩니다.