FreeTextTable multiple Columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim - 07 Jul 2004 23:16 GMT

I'm trying to do a FREETEXTTABLE search on two columns.

I can get it to work correctly with searching only one column.

I basically need it to do an OR on the two columns and haven't been
able to find anything on that. It seems like I'm limited to finding
matches that have it in both columns.

What I have so far is:

SELECT a.Name, b.rank, c.rank
FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
FreeTextTable(Test, SolutionProblem, 'author) AS c

I picture it being in the WHERE clause as:

WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
SolutionProblem, 'author'))

but I haven't found an example where I can put it in the where clause.

Thanks for any help,
Tim

Reply to this Message

John Kane - 08 Jul 2004 04:42 GMT

Tim,
Below is an example of using FREETEXTTABLE with two columns from the same
table (Northwind's Employee) with an OR condition on the join of the KEYs:

use Northwind
go
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'psychology') as A,
containstable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 4 rows

Note, that with the below freetexttable query using 'include' as the search
word that 'includes' is retuned as freetext will often return more
"imprecise" results as would a samilar contains query.

SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'include') as A,
freetexttable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 2 rows

Hope this helps!
Regards,
John

> I'm trying to do a FREETEXTTABLE search on two columns.
>
[quoted text clipped - 19 lines]
> Thanks for any help,
> Tim

Reply to this Message