CODEWITHSUNDEEP
sqlsql-serverfull-text-search
Giox
Giox

Reputation: 5133

SQL Server Fulltext search with ranking on multiple fields

I'm trying to build a very simple search engine for a library.

I have a table [Tales] that contains the following fulltext indexed columns TITLE, AUTHOR and GENRE.

The user can enter a search phrase like this: "american war by david potter"

I split each word building the following query:

SELECT TOP (100) 
    [title],
    [author],
    [genre],
    [TaleID],
    k.[RANK]
FROM 
    [Books].[dbo].[Tales] AS t 
INNER JOIN 
    CONTAINSTABLE([Tales] , *, '"history" OR "america" OR "war" OR "by" OR "david" OR "potter"') AS k ON t.TaleID = k.[key]
ORDER BY 
    k.[RANK] DESC

and the result is the following:

enter image description here

The highlighted line, row No.28, should be the one with the higher ranking, as it contains all the words searched by the user. Instead it seems that the fulltext engine prefer the multiple occurrences of the same word "american".

What's wrong with my query?

EDIT: trying with FREETEXTTABLE, the results are the same. But adding more terms I can get the right result on top:

SELECT TOP (1000) 
    [title],
    [author],
    [genre],
    [publisher],
    [storyteller],
    [TaleID],
    k.[RANK]
FROM 
    [Books].[dbo].[Tales] AS t 
INNER JOIN 
    FREETEXTTABLE([Tales], ([title],[author],[genre]), 'history of america''s civil war by david potter') as k ON t.TaleID = k.[key]
ORDER BY
    k.[RANK] DESC;

Upvotes: 0

Views: 1733

Answers (2)

LawMan
LawMan

Reputation: 3625

Declare @text varchar(255)

SELECT
    distinct coalesce(s1.rank, 0) + coalesce(s2.rank, 0) + coalesce(s3.rank, 0) as TotalRank,
    [title],
    [author],
    [genre],
    [TaleID]
FROM 
    [Books].[dbo].[Tales]
    inner join freetexttable(dbo.Tales, Title, @text) s1 on s.id = s1.[Key]
    inner join freetexttable(dbo.Tales, Author, @text) s2 on s.id = s2.[Key]
    inner join freetexttable(dbo.Tales, Genre, @text) s3 on s.id = s3.[Key]
    where (coalesce(s1.rank, 0) + coalesce(s2.rank, 0) + coalesce(s3.rank, 0)) > 0
    order by TotalRank desc

Upvotes: 0

Marc0
Marc0

Reputation: 191

Here is a link to the documentation of the ranking algorithms and Query with Full-Text Search

From the documentation:

CONTAINS/CONTAINSTABLE
Match single words and phrases with precise or fuzzy (less precise) matching. You can also do the following things: Specify the proximity of words within a certain distance of one another. Return weighted matches. Combine search conditions with logical operators.

FREETEXT/FREETEXTTABLE
Match the meaning, but not the exact wording, of specified words, phrases, or sentences (the freetext string). Matches are generated if any term or form of any term is found in the full-text index of a specified column.

Upvotes: 1

Related Questions