表A，表B一对多关系，但要查询一对一的数据

[Rex_man]

表Question(id,question)
表Answer(QuestionID,Answer,isBest)
一条Question可以有多条Answer,但是IsBest为1的数据只能一条，


现要查询一条Question对应一条Answer,(如果Answer有Isbest ＝1 的就选，没有就选第一条，没有数据就为空)

[唐诗三百首]

try this,

select a.id,a.question,isnull(b.Answer,'') 'Answer'
 from Question a
 left join
 (select * from Answer where isBest=1) b on a.id=b.QuestionID

[lzw_0736]

select *,
isnull(
(select Answer from Answer where QuestionID=a.id and isBest=1)
,
isnull((select top 1 Answer from Answer where QuestionID=a.id order by QuestionID),'')
) Answer
from Question a

[Andy__Huang]

select a.*,b.* from Question a left join Answer b on a.id=b.QuestionID and b.IsBest=1

[Shawn]

select 
	A.*,
	Answer=isnull((select top 1 Answer from Answer b where B.QuestionID=a.id order by isBest DESC, QuestionID),'')
from Question A

[Rex_man]

引用 1 楼 lzw_0736 的回复:

select *,
isnull(
(select Answer from Answer where QuestionID=a.id and isBest=1)
,
isnull((select top 1 Answer from Answer where QuestionID=a.id order by QuestionID),'')
) Answer
from Question a

牛B啊，这个可以有，但是有一点就是我用的数据库是2000的，这个不能查数据类型为ntext,text,image的字段……去改数据库了……