Full text search involving 2 tables

Question

Hi, I'm very noob in relation to Full Text search and I was told to do a full text search over 2 tables and sort results by relevance.

I will look on table "Posts" and table "PostComments". I must look for the search term (let's say "AnyWord") on Posts.Description, Posts.Title and PostComments.Comments.

I have to return Posts order by relevance but since I'm looking on Posts AND PostComments I don't know if this make sense. I'd say I need all the information on the same table in order to sort by relevance.

Could you help me to figure out if this make sense and if it does how to achieve it?

EDIT

I'll try to explain a little better what I need.

A Post is relevant for the search if the searched term is present on the title, on the description or on any of the related PostComments.

But on the front end I will show a list of post. The title of the post on this list is a link to the post itself. The post comments are visible there but not on the search result list, although they are involved on the search process.

So you could have posts on the search result that matched JUST because the search term is present on one or more comments

Answer 1

Only ContainsTable returns an evaluation of relevance. You did not mention what needed to be returned so I simply returned the name of the table from where the value is stored along with the given table's primary key (you would replace "PrimaryKey" with your actual primary key column name e.g. PostId or PostCommentsId), the value and its rank.

Select Z.TableName, Z.PK, Z.Value, Z.Rank
From    (
        Select  'Posts' As TableName, Posts.PrimaryKey As PK, Posts.Description As Value, CT.Rank
        From Posts
            Join ContainsTable( Posts, Description, 'Anyword' ) As CT
                On CT.Key = Posts.PrimaryKey
        Union All
        Select  'PostComments', PostComments.PrimaryKey,  PostComments.Comments, CT.Rank
        From PostComments
        Join ContainsTable( PostComments, Comments, 'Anyword' ) As CT
                On CT.Key = PostComments.PrimaryKey
        ) As Z
Order By Z.Rank Desc

EDIT Given the additional information, it is much clearer. First, it would appear that the ranking of the search has no bearing on the results. So, all that is necessary is to use an OR between the search on post information and the search on PostComments:

Select ...
From Posts
Where Contains( Posts.Description, Posts.Title, 'searchterm' )
    Or Exists   (
                Select 1
                From PostComments
                Where PostComments.PostId = Posts.Id
                    And Contains( PostComments.Comments, 'searchterm' )
                )