SQL server full text indexing, ranking and joining to other tables

Databases
1

i searched the net about sql server full text indexing and ranking and got this sample sql statement and it works using 1 table only:

SELECT myTable1.id, myTable1.Title, fulltextSearch.Rank
FROM myTable1
JOIN
FreeTextTable(myTable1, [myField1], ‘awesome ranking’) fulltextSearch
ON
myTable1.id = fulltextSearch.[KEY]
ORDER BY Rank DESC

what i do not know is how to make it to work when it is joined to another table. how do i do that if i have these sample tables?

myTable1

id myField1
1 rrrrrrr
2 qqqqqqq
3 kkkkkkk
.
.
.
.

myTable2

id myTable1Foreign
1 4
3 6
2 1
.
.
.

2

How do you want to join it? What do you want to do with the second table?

3

try this –

SELECT myTable1.id
     , myTable1.Title
     , fulltextSearch.Rank
     , myTable2.somecolumn
  FROM myTable1
INNER
  JOIN FreeTextTable(myTable1,[myField1],'awesome ranking') 
       AS fulltextSearch
    ON fulltextSearch.[KEY] = myTable1.id
INNER
  JOIN myTable2
    ON myTable2.myTable1Foreign = myTable1.id
ORDER 
    BY fulltextSearch.Rank DESC
4

thanks very much R937. it worked!