连接查询的一点问题

[yumanqing]

表A
code name
001 aaa
002 bbb
003 ccc
004 ddd
表B
code qty
001 200
004 500

现在想得到的查询结果是如果是表A的全部数据,如果是表B有的从表B提取数据,没有从表A提取
即:
code name qty
001 aaa 200
002 bbb
003 ccc
004 dddd 500

[paoluo]

LEFT JOIN

[paoluo]

Select
A.code,
A.name,
B.qty
From
A
Left Join
B
On A.code = B.code

[yumanqing]

其实我的原查查询语句是这样的
select cup.ZNO,cu.cCode,cu.cName,cu.cClass,cup.iYear,cup.PlanMoney
from cu_customerBasic cu
left join cu_customerYearplan cup
on cu.cCode=cup.cCode and cup.iYear=2007 and
cu.DistrictCode like '05%' AND
cu.OperationCode like '01%'

即还要从cu_customerBasic表中筛选符合条件的，可是每次都是全部的？怎么回事

[paoluo]

try

select cup.ZNO,cu.cCode,cu.cName,cu.cClass,cup.iYear,cup.PlanMoney
from cu_customerBasic cu
left join cu_customerYearplan cup
on cu.cCode=cup.cCode and cup.iYear=2007
Where
cu.DistrictCode like '05%' AND
cu.OperationCode like '01%'

[waitxing]

--建立测试数据
declare @a table(code varchar(10),name varchar(10))
insert into @a select '001','aaa'
insert into @a select '002','bbb'
insert into @a select '003','ccc'
insert into @a select '004','ddd'
declare @b table(code varchar(10),qty int)
insert into @b select '001',200
insert into @b select '004',500
--执行查询
select A.*,B.qty from @a A left join @b B on A.code=B.code
--结果
code name qty
--- ---- ----
001 aaa 200
002 bbb NULL
003 ccc NULL
004 ddd 500

[yumanqing]

paoluo(一天到晚游泳的鱼)
感谢,多一个WHERE

[ojuju10]

用left join

select a.code,a.name,b.qty from a left join b
on a.code=b.code
order by a.code