SQL Server - How do I query a table for matches with keywords from another table?

Question

I have two tables: Job, and JobKeyword. JobKeyword contains three fields: Id, JobId and Keyword, so every Job has its JobKeyword-s. How can I use SQL Server Full Text Search so it will query the Job table, with keywords from the JobKeyword table?

This is my current SQL:

WITH JobRN AS 
(
    SELECT TOP (@End) searchTable.*, ROW_NUMBER() OVER (ORDER BY searchTable.RANK) AS RowNum
    FROM FREETEXTTABLE([Job], *, @Keywords) AS searchTable 
    ORDER BY RANK
)

SELECT * FROM [Job]
INNER JOIN JobRN ON Job.Id = JobRN.[Key]
WHERE RowNum BETWEEN @Start AND @End
ORDER BY [DATE]

Currently, it searches by all fields in the Job table. How can I change it so it will search by JobKeyword table (but returns Job results of course)?

Thanks.

Answer 1

I'm not big with free text indexes but since no-one else has replied.

Without more details this is a bit of guess, but it sounds like you just want to replace FREETEXTTABLE([Job], *, @Keywords) with FREETEXTTABLE([JobKeyword], *, @Keywords).

But that assumes there is a freetext index on it.