比较有难度的SQL,100分求助!!!!

[benzhuer]

样本数据
表1：有三列,编码、名称和上级编码
ID NAME PID
301 工资福利支出 #
30101 基本工资 301
30102 津贴补贴 301
30103 奖金 301
30104 社会保障缴费 301
3010401 医疗保险 30104
3010402 养老保险 30104
3010403 失业保险 30104

表2：有很多列，这里只列出与表1相关联的列
ID
30102
3010403

现在要写个SQL查询（存储过程也可以），需要查询出的结果为：
ID NAME PID
301 工资福利支出 #
30102 津贴补贴 301
30104 社会保障缴费 301
3010403 失业保险 30104
也就是说，结果要根据表2的ID从表1中取出相对应的数据，并且要有完整的级次，不知道我表述清楚没有

[IT葱头]

不知道我理解的对不对呀。。。。
select t1.*
from table1 t1,table2 t2
where t1.id = t2.id
order by t1.id,t1.pid

[forgetsam]

select ID NAME PID from t1
connect by id = prior pid
start with pid in (select id from t2)

[forgetsam]

select ID,NAME,PID from t1  
connect by id = prior pid
start with pid in (select id from t2)

[huan_lxyd]

select distinct ID,NAME,PID from 表1
connect by id = prior pid
start with id in (select id from 表2)
order by id

[rgcsm]

7楼正解

[benzhuer]

谢谢,结帖给分

[木小丰~]

select *
from table1 t3
where t3.id in (select t1.pid from table1 t1 , table2 t2
where t1.id = t2.id)
union
select t5.id,t5.pid from table1 t5 , table2 t6
where t5.id = t6.id;

能实现，感觉有点麻烦，不知道有没有简单点的方法。

[木小丰~]

select *
from table1 t3
where t3.id in (select t1.pid from table1 t1 , table2 t2
where t1.id = t2.id)
union
select t5.id,t5.pid from table1 t5 , table2 t6
where t5.id = t6.id;

能实现，感觉有点麻烦，不知道有没有简单点的方法。

[木小丰~]

select *
from table1 t3, (select t1.id,t1.pid from table1 t1 , table2 t2
where t1.id = t2.id) t4
where t3.id = t4.pid or t3.id = t4.id;