sql server full text search:
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 [quoted text, click to view] "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! [quoted text, click to view] "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...
> > 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!
> >
> >
>
>
Don't see what you're looking for? Try a search.
|
