FREETEXTTABLE

The following FREETEXTTABLE query works, but I'm getting all rows returned.
The highest ranked are
at the top like expected, but it's also returning all rows in the Stock
table that have no match whatsoever. In my FREETEXT query (bottom), all the
results
had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
expecting a temp table to be created with the results. However, the code
below is actually joining the created table. I don't get it.
How do I get only matching results in the FREETEXTTABLE?
thanks!
-- This FREETEXTABLE query returns all row in the Stock table. Regardless of
any matches.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = 'midler'
SELECT
Stock.OrderNo,
Stock.Description,
Stock.Category,
Stock.s_Type,
Stock.Manuf,
Stock.Label,
Titles.Title,
Titles.Artist,
Hardware.m_Specs,
Stock.ManCode
FROM
Stock
LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
ORDER BY
FS_TABLE.Rank DESC
--This FREETEXT query works as expected
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler" '
SELECT Stock.OrderNo, Stock.Description, Stock.Category,
Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
Titles.Artist, Hardware.m_Specs, Stock.ManCode
FROM Stock LEFT OUTER JOIN
Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
Hardware ON Stock.OrderNo = Hardware.OrderNo
WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
FREETEXT(Stock.Description,@.SearchCriteria) OR
FREETEXT(Stock.Category,@.SearchCriteria) OR
FREETEXT(Stock.s_Type,@.SearchCriteria) OR
FREETEXT(Stock.Manuf,@.SearchCriteria) OR
FREETEXT(Stock.Label,@.SearchCriteria) OR
FREETEXT(Stock.ManCode,@.SearchCriteria) OR
FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
FREETEXT(Titles.Title,@.SearchCriteria) OR
FREETEXT(Titles.Artist,@.SearchCriteria)
FREETEXTTABLE returns a table of all the keys and their ranking for your
search. Based on what you are saying, I am guessing that it also returns
items that don't match and they probably have a ranking of 0.
So you can filter out those by adding a WHERE clause like this:
WHERE
FS_TABLE.RANK > 0
For me, I use where it's greater than 20 since I only care about the 80%
that are relevant (in my case).
Hope that helps.
Brien King
"shank" <shank@.tampabay.rr.com> wrote in message
news:%23y$G1uFYEHA.1264@.TK2MSFTNGP11.phx.gbl...
> The following FREETEXTTABLE query works, but I'm getting all rows
returned.
> The highest ranked are
> at the top like expected, but it's also returning all rows in the Stock
> table that have no match whatsoever. In my FREETEXT query (bottom), all
the
> results
> had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
> expecting a temp table to be created with the results. However, the code
> below is actually joining the created table. I don't get it.
> How do I get only matching results in the FREETEXTTABLE?
> thanks!
> -- This FREETEXTABLE query returns all row in the Stock table. Regardless
of
> any matches.
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = 'midler'
> SELECT
> Stock.OrderNo,
> Stock.Description,
> Stock.Category,
> Stock.s_Type,
> Stock.Manuf,
> Stock.Label,
> Titles.Title,
> Titles.Artist,
> Hardware.m_Specs,
> Stock.ManCode
> FROM
> Stock
> LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
> LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
> LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
> ORDER BY
> FS_TABLE.Rank DESC