SQL Full text search with foreign key columns

Question

I have a table (table 1) with couple of column on which i want indexed.
There is also ArticleID column (foreign key), with this id I want to use join to retrieve "articleTitle" from the article Table (table 2), and of curse be able to search in this column.

How do I create this kind of index?
Do I need to index both tables?
and how should i write the select query for that?

Answer 1

You need to create a view and index that

I spent a lot of time on this a while back and the view was the only way I was able to do this.

I also ended up putting everything in 1 column. By this I mean that I added all the text from each column into one column. For example you have a simple table with ID, first, middle, last name columns. In my view I would condense them to 1 column in my view. So for a row of

ID:1 - First Name: Joe - Middle Name: Bob - Last Name: Joe

In the view you would see one column with

1 Joe Bob Joe

This was because I found that some records were not being returned if they were not in ALL columns. Thus by condensing it down to 1 column this problem was eliminated.