简单的SQL联接

[Alessandro_]

有三张基础资料表
表A
dm_a     mc_a
01      a_mc1
02      a_mc2
....
表B
dm_b     mc_b
001     b_mc1
002     b_mc2
...
表C
dm_c     mc_c
0001     c_mc1
0002     c_mc2
....
有两张数据表
数据表SJ1
dm_a   dm_b    dm_c   sj1   
01      001    0001   100.00
...
数据表SJ2
dm_a   dm_b    dm_c   sj2   
01      001    0001   50.00
02      001    0002   10.00
...

希望得到结果表
dm_a   dm_b    dm_c   sj1       sj2
01      001    0001   100.00    50.00
02      001    0002    0.00     10.00
..

结果表是一定要通过三张基础资料表来关联吗？
还是可以直接通过两张数据表来关联？

感谢各位啦！

[Alessandro_]

自己想办法解决。

[Shawn]

SELECT
	dm_a = COALESCE(a.dm_a, b.dm_a),
	dm_b = COALESCE(a.dm_b, b.dm_b),
	dm_c = COALESCE(a.dm_c, b.dm_c),
	sj1 = ISNULL(a.sj1, 0),
	sj2 = ISNULL(b.sj2, 0)
FROM SJ1 a
FULL JOIN SJ2 b
	ON a.dm_a = b.dm_a
	AND a.dm_b = b.dm_b
	AND a.dm_c = b.dm_c

[Alessandro_]

人呢？

[昵称90天可改一次]

select a.dm_a,
		a.dm_b,
		a.dm_c,
		sj1=CASE when b.sj1 IS NULL then '0'
			else b.sj1
			end,
		sj2=CASE when a.sj2 IS NULL  then '0'
			else a.sj2
			end
from SJ2 a
	LEFT JOIN SJ1 b
	ON a.dm_a=b.dm_a
/*
dm_a   dm_b    dm_c		sj1		sj2	
01		001		0001	100.00	50.00
02		001		0002	0		10.00
*/

[Alessandro_]

引用 2 楼 chwnrthd 的回复:

select a.dm_a,
		a.dm_b,
		a.dm_c,
		sj1=CASE when b.sj1 IS NULL then '0'
			else b.sj1
			end,
		sj2=CASE when a.sj2 IS NULL  then '0'
			else a.sj2
			end
from SJ2 a
	LEFT JOIN SJ1 b
	ON a.dm_a=b.dm_a
/*
dm_a   dm_b    dm_c		sj1		sj2	
01		001		0001	100.00	50.00
02		001		0002	0		10.00
*/

您这样不行吧，因为sj2表不一定全部是都在sj1表有体现！ 有可能是sj1表有，sj2表没有；或者是sj1表没有，sj2表有的情况。