请教一个SQL语句的写法

[dhxyxiaofeng]

我有两个表

A 问卷题库表 - ResearchContent(调查内容),ResearchCode(内容编码)
B 调查记录表 - PeopleCode(接受调查者编码),ResearchCode(内容编码),OutCome(结果)

我想得到这个的结果

表A中有四个问题．
ResearchCode ResearchContent
１　 n1?
２　 n2?
３　 n3?
４　 n4?

表Ｂ中只回答了一个问题
PeopleCode ResearchCode OutCome
0001 3 是

我想查询出People - 0001 对所有问题的回答结果，包括没回答的和已回答的．
两个表做个联合查询得出以下表：

例如：
ResearchCode ResearchContent PeopleCode OutCome
１　 n1? 0001 null
２　 n2? 0001 null
３　 n3? 0001 是
４　 n4? 0001 null

感觉不是很复杂，自己写半天也写不出．只好麻烦大家了．

请帮个忙，Thank you so much

[kaihuababy]

学习！

[endresult]

(select ResearchCode,ResearchContent,PeopleCode,OutCome
from A,B
where A.ResearchCode = B.ResearchCode)
unit
(select * from A)

[sqllong]

--创建环境

create table A(ResearchCode int,ResearchContent varchar(1000))
insert into     A  select 1,'调查1' 
             union select 2,'调查2'
             union select 3,'调查3'
             union select 4,'调查4'

create table B(ResearchCode int,PeopleCode int, OutCome varchar(1000))
insert into     B  select 1,0001,NULL 
             union select 2,0001,NULL
             union select 3,0001,'是'
             union select 4,0001,NULL

--执行语句

select B.ResearchCode,A.ResearchContent,B.PeopleCode,B.OutCome 
from A inner join B on A.ResearchCode = B.ResearchCode

--（所影响的行数为 4 行）
ResearchCode   ResearchContent     PeopleCode   OutCome                                                                    
1                   调查1	       1	NULL	
2                   调查2	       1	NULL	
3                   调查3	       1	是	
4                   调查4	       1	NULL	

[dhxyxiaofeng]

是不是语法有错误?我没调通.

[sqllong]

少了where 字句

--执行语句

select B.ResearchCode,A.ResearchContent,B.PeopleCode,B.OutCome 
from A inner join B on A.ResearchCode = B.ResearchCode
where B.PeopleCode = 0001

[sqllong]

如果LZ的PeopleCode 字段的类型为varchar的话，记得改为where B.PeopleCode = '0001'

[dhxyxiaofeng]

谢谢,不过感觉好像有点出入.

我的B表中只有一条记录,而不是四条.

[hecong875]

找到2个表的关联字段!
然后用左连接的方法来写!就可以!

[sqllong]

--删除表

drop table A,B

--创建环境

create table A(ResearchCode int,ResearchContent varchar(1000))
insert into     A  select 1,'调查1' 
             union select 2,'调查2'
             union select 3,'调查3'
             union select 4,'调查4'

create table B(ResearchCode int,PeopleCode int, OutCome varchar(1000))
insert into     B  select 3,0001,'是'

--执行语句

declare @PeopleCode int
set @PeopleCode = 0001
select ResearchCode,ResearchContent,PeopleCode,OutCome from 
(select A.ResearchCode,A.ResearchContent,isnull(PeopleCode,@PeopleCode) as PeopleCode,B.OutCome 
from A  left join B on A.ResearchCode = B.ResearchCode) as c
where c.PeopleCode = @PeopleCode

--（所影响的行数为 4 行）

ResearchCode   ResearchContent     PeopleCode   OutCome                                                                    
1                   调查1	       1	NULL	
2                   调查2	       1	NULL	
3                   调查3	       1	是	
4                   调查4	       1	NULL