May 29, 2006 at 11:43 am
Hi everybody, I am having a problem with figuring out how to best implement a keyword search in a database of books(I have to create a web interface for the search). I dont know how to create the search query, to get the most relevant results. For example if I would use WHERE CONTAINS and then (*, word1 NEAR word2 NEAR ...) I will get pretty good results, but I will get only results which include all the words included in the query. I would need that it would return also partial results. When I use the WHERE FREETEXT ("search phrase"), I get all the results, but many of them are not as relevant. How should I solve this problem? I have been trying to solve this and not luck so far. I have the fulltext search allowed on the database and programing in asp.net. thanks a lot for your help
May 30, 2006 at 2:59 am
Why not search twice. Once with all the words and then again with some of the words. Give a relevence score for each word matched and order descending.
May 30, 2006 at 5:26 am
and how would you implement that? would you search for all the combinations of the words?
May 30, 2006 at 6:52 am
Your WHERE clause can use multiple CONTAINS(), if necessary, possibly separated by OR. You could also union queries together. Or, run one or more queries as derived tables - providing the most general results using the partial matching - which is then further filtered using the main query.
May 30, 2006 at 7:00 am
#Quote
Or, run one or more queries as derived tables - providing the most general results using the partial matching - which is then further filtered using the main query.
#Quote
How could I do that? thanks a lot for your help!
May 30, 2006 at 7:59 am
is there a way how I could join at first all the columns in one row into one long string, then perform the search on it and then return the table again in unchanged form?
Example:
Title Author
----- ------
sql mm
and the search would be perfomed on the string "sqlmm"
Thanks a lot!
May 30, 2006 at 10:39 am
Another problem(this time hopefully easier) which I am trying to deal with right now...How do you do a fulltext search on multiple(specified) columns? For example, FREETEXT(column1+column2, 'search_string'). How do you specify those 2 columns, so that it would work? I know how to specify all, and just one column, but I need to specify just few of them and I am not able to do that. Thanks a lot.
May 30, 2006 at 11:20 am
SELECT ....
FROM YourTable
WHERE Freetext(column1, 'search_string')
OR Freetext(column2, 'search_string')
OR Freetext(column3, 'search_string')
...
May 30, 2006 at 11:33 am
The query layouts I mentioned in my earlier replies regarding derived tables include many different structures. Here is one:
SELECT n.pkey, n.subject
FROM status n
JOIN (SELECT pkey
FROM documents
WHERE Freetext(occtext, 'wake vortex')
OR Freetext(rectext, 'wake vortex')
OR Freetext(resptext, 'wake vortex')
) AS kw
ON n.pkey = kw.pkey
May 30, 2006 at 11:59 am
That seems to work. Thanks a lot for your help. However, I need the results to be displayed according to rank, and when I use multiple or statements, it does not work(I think). I have though about concatening few columns into one(called FullName) and then performing the search, but I am getting this error:
Invalid column name 'KEY'.
Invalid column name 'FullName'.
This is my query:
SELECT Title, [Last Name - 1], [First Name - 1], ([Last Name - 1]+' '+[First Name - 1]) AS FullName from tbl_books where FreeTEXT(FullName,'Bonnie')
If I put into the freetext something else, for example Title, it works fine. What can be the problem?
May 30, 2006 at 12:16 pm
First of all, I would discourage using column names like [Last Name - 1].
Freetext() only works on columns that have been registered for full-text searching. You can't use a computed column with Freetext(). As I mentioned earlier, you would have to do this (assuming Last name and First name are FT indexed):
SELECT Title, [Last Name - 1], [First Name - 1], ([Last Name - 1]+' '+[First Name - 1]) AS FullName
FROM tbl_books
WHERE FreeTEXT([Last Name - 1],'Bonnie')
OR FreeTEXT([First Name - 1],'Bonnie')
May 30, 2006 at 12:25 pm
Oh, I forgot about that. Those name are very weird I know, unfortunately I the database system was already created before I came here and I just have to create a search engine. Thanks a lot for your help and sorry for bothering you so much
May 30, 2006 at 12:39 pm
You mentioned sorting by rank. Did you try using FreeTextTable()? That returns rank information - Freetext() does not.
Here's an example:
DECLARE @search varchar(100)
SET @search = 'Bonnie'
SELECT FT.Rank
, Title
, [Last Name - 1]
, [First Name - 1]
, ([Last Name - 1]+' '+[First Name - 1]) AS FullName
FROM tbl_books
JOIN FreeTextTable(tbl_books, [Last Name - 1], @search) AS FT
ON tbl_books.<pkey> = FT.[Key]
UNION
SELECT FT.Rank
, Title
, [Last Name - 1]
, [First Name - 1]
, ([Last Name - 1]+' '+[First Name - 1]) AS FullName
FROM tbl_books
JOIN FreeTextTable(tbl_books, [First Name - 1], @search) AS FT
ON tbl_books.<pkey> = FT.[Key]
ORDER BY Rank DESC, title
May 30, 2006 at 1:10 pm
At the end, I intended to use the FREETEXTTABLE or CONTAINSTABLE, I was just trying to get the multiple column search running with FREETEXT. But what you have just posted is exactly something I was trying to do after you told me that the FREETEXT cannot search within made up columns. I am using the exact syntax as you wrote here, I have just added 2 more unions for [Last Name - 2] and [First Name - 2]. The problem now is that I am getting some duplicate results. How could I eliminate them?
May 30, 2006 at 4:34 pm
One more question and hopefully final. I have multiple search fields with different options(keyword, author, title, ...) and for each of them I finally have a query which works pretty well. But now I need to put some of them together thorough logic(like search for the author AND title). just putting them next to each other and trying to make subqueries out of them did not work(or more probably I was just not able to get the right syntax). And also, all of those queries are either FREETEXTTABLE or CONTAINSTABLE, so they have a ranking assigned which I would like to keep for the final table displayed. Just for illustration, here are my 2(at the end will be more) queries, which I would need to combine with AND into a final query which I would display to the user:
Query1:
"SELECT FT_TBL.Title, FT_TBL.[First Name - 1], FT_TBL.[Last Name - 1], KEY_TBL.RANK FROM tbl_books AS FT_TBL INNER JOIN CONTAINSTABLE (tbl_books, *,'" + keyword + "') AS KEY_TBL ON FT_TBL.ID = KEY_TBL. WHERE KEY_TBL.RANK > " + rank.ToString() + " ORDER BY KEY_TBL.RANK DESC"
Query2:
"SELECT FT_TBL.Title, FT_TBL.[First Name - 1], FT_TBL.[Last Name - 1], KEY_TBL.RANK FROM tbl_books AS FT_TBL INNER JOIN FREETEXTTABLE (tbl_books, Title,'" + input + "') AS KEY_TBL ON FT_TBL.ID = KEY_TBL. WHERE KEY_TBL.RANK > " + rank.ToString() + " ORDER BY KEY_TBL.RANK DESC"
Thanks a lot!
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply