求一个简单的SQL语句 请大家帮忙

[_1_]

正在编程中 遇到一点小问题 请大家帮帮忙
SQL SERVER 2000
2个表

表名：order
idd spgg sl
1 608 200
2 608 200



表名：orderMX
reidd spgg jhsl
1 608 100


返回结果：
idd spgg sl jhsl
1 608 200 100
2 608 200 0

[叶子]

select a.*,isnull(b.jhsl,0)
from [order] a left join orderMX b on a.idd =b.reidd  

[zhangweitc123]

select idd,spgg,sl,isnull(jhsl,0)jhsl from order t1,orderMX t2 where t1.idd=t2.idd

[chenyukuai]

select a.idd,a.spgg,a.sl,case when b.jhsl is null then 0 else b.jhsl end jhsl 
from order a left join orderMX b on a.idd=b.reidd 

[zhangweitc123]

SQL code
select t1.idd,t1.spgg,sl,isnull(jhsl,0)jhsl from order t1,orderMX t2 where t1.idd=t2.idd

[叶子]

--测试数据
declare @order table (idd int,spgg int,sl int)
insert into @order
select 1,608,200 union all
select 2,608,200
--测试数据
declare @orderMX table (reidd int,spgg int,jhsl int)
insert into @orderMX
select 1,608,100 

select a.*,isnull(b.jhsl,0) as jhsl 
from @order a left join @orderMX b on a.idd=b.reidd

/*
idd         spgg        sl          jhsl
----------- ----------- ----------- -----------
1           608         200         100
2           608         200         0
*/

[zhangweitc123]

[Quote=引用 4 楼 zhangweitc123 的回复:]
SQL code

SQL code
select t1.idd,t1.spgg,sl,isnull(jhsl,0)jhsl from order t1,orderMX t2 where t1.idd=t2.idd
[/Quote]
错误在于idd=2时，没有t1.idd=t2.reidd来匹配，只能查到1条语句，还是要用连接

[_1_]

谢谢大家 分不多 就只好平均分分了