求T-SQL语句（关于“关键字搜索”）

[madpolice]

背景：对一个论坛的贴子内容做关键字搜索。

数据库：MS SQL SERVER

表的结构：
表名 articles
字段 id 整型，自动递增，主键（存放贴子编号）
r_id 整型，默认值0（存放贴子的父贴编号。主贴的r_id设为0；回帖r_id等于其所关联的主贴的id。）
title 文本（存放贴子主题）
content 文本（存放帖子内容）

要求：
1.对content字段进行关键字搜索，关键字为 MYKEYWORD
2.返回一个记录集！！！
3.如果主贴中包含关键字，则返回该主贴的id,title
4.如果回帖中包含关键字，则返回该回帖所关联的主贴的id,title！！！
5.返回的记录集中没有重复记录！！！

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
本人学艺不精，想了两天也没有想出来。
恳请各位高手指点。

madpolice

[saucer]

the preferred way is to use full-text search, see
Full Text Searching with SQL Server 2000
http://tutorials.findtutorials.com/read/id/416

but try

SELECT id, title
FROM articles
WHERE id in
(SELECT CASE WHEN r_id = 0 THEN id ELSE r_id END
FROM articles WHERE content LIKE '%MYKEYWORD%')

[madpolice]

saucer:

I am very touched and appreciate your help! Your code is just what I need. I have learned more about CASE statement from your reply.
I cannot using full-text search for some reason, but your advice is very good anyway.
Thank you very much!