FREETEXT vs FREETEXTTABLE

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!
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
"shank" <shank@.tampabay.rr.com> wrote in message
news:esmKmpsXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> 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!
>
|||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!
"news.microsoft.com" <spammehere@.arcaderestoration.com> wrote in message
news:e4dx4uuXEHA.3420@.TK2MSFTNGP12.phx.gbl...
> 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
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:esmKmpsXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> there
>