implementing keyword search in database of books

[mm2ha]

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

 

[vbkris]

>>but I will get only results which include all the words included in the query

how exactly should the results be and on what basis???

Known is handfull, Unknown is worldfull

 

[mm2ha]

In the keyword, the search should be lookng thorough all the fields in the database(title, authors name, call number,...). The results should be ordered by the number of words of the search phrase whithin the row in the table. So for example, when the user searches for "dan brown angels and deamons", it will list as the first book the one by dan brown angels and deamons, then maye some other book called angels and deamons, then other books by dan brown and so on. I hope its more clear what I am trying to do now. basically trying to get a general keyword search on a database of books.

 

[vbkris]

i think the best way to do that would be to split the multiple words into single words, do a like query for each word and do a UNION on the results...

Known is handfull, Unknown is worldfull

 

[mm2ha]

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!

 

[vbkris]

yes u can, try this:

select * from Tbl where Title+Author like 'asdasd%'

Known is handfull, Unknown is worldfull

 

[mm2ha]

That worked pretty well. However, when I want to use the new created column FullName (I used the concatenation for creating new column) it gives me 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?

 

[vbkris]

>>[Last Name - 1]

wht does that mean???

Known is handfull, Unknown is worldfull

 

[mm2ha]

It is just a column in the table. It should be correctly LastName_1 or something like that. They just called it like that(whoever created the database)

 

[vbkris]

where FreeTEXT(FullName,'Bonnie')


should be:
where FreeTEXT(([Last Name - 1]+' '+[First Name - 1]) e,'Bonnie')


Known is handfull, Unknown is worldfull