请问这样的查询(递归树)如何处理？

表A(多级机构表)有三个字段：
ID Parentid Name
1   0            机构A
2   1            机构B
3   2            机构C
4   3            机构D
表B(车辆表)中也有三个字段：
ID Parentid（对应表A中的ID） DESC
1   4                                        车A
2   2                                        车B
3   3                                        车C

这样，要想一次性把所有信息都查出来。比如，我现在要查出机构A的所有车辆能不能用一条语句搞定。

 

 

 

 

SQL> create table table_a ( id number,parentid number,name varchar2(10));

Table created
SQL> insert into table_a values ( 1,0,'机构A');

1 row inserted
SQL> insert into table_a values ( 2,1,'机构B');

1 row inserted
SQL> insert into table_a values ( 3,2,'机构C');

1 row inserted
SQL> insert into table_a values ( 4,3,'机构D');

1 row inserted
SQL> commit;

Commit complete

SQL>
SQL> create table table_b ( id number,parentid number,descr varchar(10));

Table created
SQL> insert into table_b values (1,4,'车A');

1 row inserted
SQL> insert into table_b values (2,2,'车B');

1 row inserted
SQL> insert into table_b values (3,3,'车C');

1 row inserted
SQL> commit;

Commit complete

SQL> select * from table_a;

        ID   PARENTID NAME
---------- ---------- ----------
         1          0 机构A
         2          1 机构B
         3          2 机构C
         4          3 机构D

SQL> select * from table_b;

        ID   PARENTID DESCR
---------- ---------- ----------
         1          4 车A
         2          2 车B
         3          3 车C


SQL> select a.name,t.descr
  2    from table_b t,
  3         (
  4          select id,lpad(name,10+level*2,' ') name
  5            from table_a
  6           start with name = '机构A'
  7          connect by prior id = parentid
  8         ) a
  9  where t.parentid = a.id
10  /

NAME                                                                             DESCR
-------------------------------------------------------------------------------- ----------
         机构B                                                                   车B
           机构C                                                                 车C
             机构D                                                               车A

SQL>

 

已利用，再次表示感谢！

SELECT V.*
  FROM V_FINALPOSITION V,
       (SELECT A.AGENCYID
          FROM T_AGENCY A
         START WITH A.AGENCYID = 0
        CONNECT BY PRIOR A.AGENCYID = A.PARENTID) T
WHERE V.PARENTAGENCYID = T.AGENCYID