A表
type_id  name  parent_id 
   1       a       0
   2       b       0
   3       c       0
   4       b1      2
   5       b2      2

B表
    id  name    type_id
   1       tt       4
   2       yy       5

--1.Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses 
--Hierarchical Queries
--START WITH and CONNECT BY PRIOR clauses.

SELECT employee_id, manager_id, first_name, last_name
FROM employee_jh
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
          1          0 James      Smith
          2          1 Ron        Johnson
          3          2 Fred       Hobbs
          5          2 Rob        Green
          4          1 Susan      Jones
          6          4 Jane       Brown
          9          6 Henry      Heyson
          7          4 John       Grey
          8          7 Jean       Blue
         10          1 Kevin      Black
         11         10 Keith      Long
         12         10 Frank      Howard
         13         10 Doreen     Penn

13 rows selected.

--2.Using a Subquery in a START WITH Clause
SELECT LEVEL,
       LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee
FROM employee_jh
START WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = 'Kevin' AND last_name = 'Black')
CONNECT BY PRIOR employee_id = manager_id;

     LEVEL EMPLOYEE
---------- -------------------------
         1  Kevin Black
         2    Keith Long
         2    Frank Howard
         2    Doreen Penn

--3.Including Other Conditions in a Hierarchical Query
SELECT LEVEL,
       LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
       last_name AS employee, salary
FROM employee_jh
WHERE salary <= 50000
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;

     LEVEL EMPLOYEE                      SALARY
---------- ------------------------- ----------
         3      Rob Green                 40000
         3      Jane Brown                45000
         4        Henry Heyson            30000
         3      John Grey                 30000
         4        Jean Blue               29000
         3      Keith Long                50000
         3      Frank Howard              45000
         3      Doreen Penn               47000

8 rows selected.

------解决方案--------------------

select B.name,B.type_id,A.parent_id from A left outer join B on
(A.type_id = b.type_id) where A.parent_id = 2;
不知道你是 只是查询 还是要写递归

------解决方案--------------------

SQL code

with a as(
select 1 type_id,'a' name,0 parent_id from dual
union all
select 2,'b',0 from dual
union all
select 3,'c',0 from dual
union all
select 4,'b1',2 from dual
union all
select 5,'b2',2 from dual
),b as(
select 1 id,'tt' name,4 type_id from dual
union all
select 2,'yy',5 from dual
)select type_id from b 
    where exists 
        (select 1 from 
            (select type_id from a where 
                type_id<>2  connect by prior type_id=parent_id start with type_id=2) c 
                 where c.type_id=b.type_id)

  TYPE_ID
----------
         4
         5

------解决方案--------------------

SQL code

SELECT * FROM tb_A
CONNECT BYPRIOR type_id  = parent_id 
START WITH type_id  = '2'