select a.id_a, a.type1, b1.id_c, a.type2, b2.id_c

from a, b b1, b b2

where a.id_a=b1.id_a

and a.type1=b1.type

and a.id_a=b2.id_a

and a.type2=b1.type

이정도면 나오지않을까요

---------------------------------------

(추가1)조건이 이해가 안되는데요

C.TABLE에 존재 하지 않거나 존재하더라도 STATUS가 3이아닌것만 뽑아내야하지요 가 아닌

B.TABLE의 ID_C의 결과가 없거나, C.TABLE의 STATUS가 3이 아닌것을 뽑아야하는건가요?

WITH a AS
(
SELECT 100 id_a, 'B' type1, 'S' type2 FROM dual
UNION ALL SELECT 200, 'B', 'S' FROM dual
UNION ALL SELECT 300, 'B', 'S' FROM dual
)
, b AS
(
SELECT 1 id_b, 100 id_a, 'B' type, 'C101' id_c FROM dual
UNION ALL SELECT 2, 100, 'S', ''     FROM dual
UNION ALL SELECT 3, 200, 'B', 'C101' FROM dual
UNION ALL SELECT 4, 200, 'S', 'C102' FROM dual
UNION ALL SELECT 5, 300, 'B', 'C101' FROM dual
UNION ALL SELECT 6, 300, 'S', 'C103' FROM dual
)
, c AS
(
SELECT 'C101' id_c, 'B' type, 3 status FROM dual
UNION ALL SELECT 'C102', 'S', 3 FROM dual
UNION ALL SELECT 'C103', 'S', 0 FROM dual
)
SELECT a.id_a
     , a.type1
     , MIN(DECODE(a.type1, b.type, c.id_c)) id_c1
     , a.type2
     , MIN(DECODE(a.type2, b.type, c.id_c)) id_c2
  FROM a
  LEFT OUTER JOIN b
    ON a.id_a = b.id_a
   AND b.type IN (a.type1, a.type2)
  LEFT OUTER JOIN c
    ON b.id_c = c.id_c
   AND b.type = c.type
   AND c.status = 3
 GROUP BY a.id_a, a.type1, a.type2
 HAVING COUNT(c.status) < 2
;