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

[tinapa]

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
.
.
.

thanks for any inputs

 

[r937]

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

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon