Free Text Query

Notice the wildcards in the query above. This indicates that trouble can appear anywhere in the text. This is very similar to a LIKE clause. The exception is that you can specify conditions within the text, such as AND and OR.

SELECT *
FROM employees 
WHERE FREETEXT (Notes, "relocated to other departments")

To construct a free-text query using FREETEXT, you could issue a statement similar to the code above.

SELECT E.LastName, E.FirstName
FROM Employees
AS E JOIN CONTAINSTABLE (Employees, Notes, '*trouble*') AS FT
ON E.EmployeeID = FT.[KEY]

If you want to use a CONTAINS query and join the results to another table, you must use the CONTAINSTABLE command. Because it returns only a Key column and Rank column, you can join the Key column with the column that represents the key in the table. This example uses the EmployeeID column as the key and returns the LastName and FirstName column for a CONTAINS query.

SELECT E.LastName, E.FirstName
FROM Employees
AS E JOIN FREETEXTTABLE (Employees, Notes, 'relocated to other departments') AS FT
ON E.EmployeeID = FT.[KEY]

If you want use a FREETEXT query and join the results to another table, you must use the FREETEXTTABLE command. Because it returns only a Key column and Rank column, you can join the Key column with the column that represents the key in the table. This example uses the EmployeeID column as the key and returns the LastName and FirstName column for a FREETEXT query.