FREETEXT vs FREETEXTTABLE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shank - 30 Jun 2004 18:47 GMT

1) The following query works fine and gives me the results I want. Is there
a better more efficient way of writing this?

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)

2) As far as I can tell, RANK is not available with FREETEXT, but is
available with FREETEXTTABLE. How can I convert the above to make use of
FREETEXTTABLE?

thanks!

Reply to this Message

news.microsoft.com - 30 Jun 2004 22:46 GMT

This should do it: NOTE: This assumes you want to search ALL fields that
are set up in the FULL-TEXT Search for that table.

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

> 1) The following query works fine and gives me the results I want. Is there
> a better more efficient way of writing this?
[quoted text clipped - 23 lines]
>
> thanks!

Reply to this Message

shank - 01 Jul 2004 15:51 GMT

The 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, 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 should do it: NOTE: This assumes you want to search ALL fields that
> are set up in the FULL-TEXT Search for that table.
[quoted text clipped - 51 lines]
> >
> > thanks!

Reply to this Message