求SQL查询语句-多表连接

[cyzhang811]

假如有以下四张表

a表 b表 c表 d表
org_code p_num org_code i_num org_code s_num t_num org_code org_name
123 13.4 123 18 123 13.4 18 123 www
124 24.7 125 32.1 124 24.7 0 124 vvv
126 32.9 136 19.2 125 0 32.1 125 sss
127 32.9 126 eee
128 19.2 127 rrr
128 uuu
136 mmm

我想把这几张表连起来，通过一个SQL查询，得到以下的结果集

结果集
org_code org_name p_num i_num s_num t_num
123 www 13.4 18 13.4 18
124 vvv 24.7 24.7 0
125 sss 32.1 0 32.1
........
136 mmm 19.2

问题描述如上，不知道我说的清楚吗？
应该不难实现，只是我水平有点凹，弄了好久都没成功。
请各位高手帮忙
小弟谢谢了。

[cyzhang811]

首先谢谢你！
d表中是全量的，但是我不是要把d表中所有的org_code都列出来，而是把abc表中出现的org_code全列出来。

谢谢

[yangxiao_jiang]

select org_code, org_name, p_num , i_num, s_num , t_num from
(
select org_code, org_name, p_num , i_num, s_num from (
select t.org_code as org_code,t.org_name as org_name,a.p_num as p_num from (
select form d where d.org_code in (
select org_code from a union
select org_code from b union
select org_code from c union)
)
) as t
left join a on t.org_code=a.org_code
) temp1 left join b on temp1.org_code=b.org_code
) as temp2 left join c on temp2.org_code=c.org_code

[yangxiao_jiang]

都是通过org_code关联的吧？

select org_code, org_name, p_num , i_num, s_num , t_num from
(
select org_code, org_name, p_num , i_num, s_num from (
select d.org_code as org_code,d.org_name as org_name,a.p_num as p_num form d left join a on d.org_code=a.org_code
) temp1 left join b on temp1.org_code=b.org_code
) as temp2 left join c on temp2.org_code=c.org_code

[KidXiangJie]

你d表里的org_code是全量的吧？

select d.org_code,d.org_name,a.p_num,b.i_num,c.s_num,c.t_num
from
d
left outer join
a
on d.org_code=a.org_code
left outer join
b
on d.org_code=b.org_code
left outer join
c
on d.org_code=c.org_code

如果你的d表里的org_code不是全量的上述方法不能取完整的数据，可以先把4张表的org_code取全后做为左表，再用left outer join

[freeboat625]

这样可不可以？
select a.org_code,d.org_name,a.p_num,b.i_num,c.s_num,c.t_num
from a,b,c,d
where a.org_code=b.org_code=c.org_code=d.org_code;