Using Full-Text Search in SQL Server 2008 across multiple tables, columns

Question

I need to search across multiple columns from two tables in my database using Full-Text Search. The two tables in question have the relevant columns full-text indexed.

The reason I'm opting for Full-text search: 1. To be able to search accented words easily (cafè) 2. To be able to rank according to word proximity, etc. 3. "Did you mean XXX?" functionality

Here is a dummy table structure, to illustrate the challenge:

Table Book
BookID
Name (Full-text indexed)
Notes (Full-text indexed)

Table Shelf
ShelfID
BookID

Table ShelfAuthor
AuthorID
ShelfID

Table Author
AuthorID
Name (Full-text indexed)

I need to search across Book Name, Book Notes and Author Name.

I know of two ways to accomplish this:

1. Using a Full-text Indexed View: This would have been my preferred method, but I can't do this because for a view to be full-text indexed, it needs to be schemabound, not have any outer joins, have a unique index. The view I will need to get my data does not satisfy these constraints (it contains many other joined tables I need to get data from).

2. Using joins in a stored procedure: The problem with this approach is that I need to have the results sorted by rank. If I am making multiple joins across the tables, SQL Server won't search across multiple fields by default. I can combine two individual CONTAINS queries on the two linked tables, but I don't know of a way to extract the combined rank from the two search queries. For example, if I search for 'Arthur', the results of both the Book query and the Author query should be taken into account and weighted accordingly.

Zsigmondy

For #1, where you say you're joining... are you really OUTER joining? that's a Cartesian product and I doubt you're actually doing that. An inner or left/right join is fine. – Rebane

I am not using an OUTER JOIN for any of the shown tables. There are other tables that I need to LEFT OUTER JOIN, because they might not have any FKed rows. – Zsigmondy

Answer 1

Using FREETEXTTABLE, you just need to design some algorithm to calculate the merged rank on each joined table result. The example below skews the result towards hits from the book table.

SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank]
FROM Book b
INNER JOIN Author a ON b.AuthorID = a.AuthorID
INNER JOIN FREETEXTTABLE(Book, Name, @criteria) bkt ON b.ContentID = bkt.[Key] 
LEFT JOIN FREETEXTTABLE(Author, Name, @criteria) akt ON a.AuthorID = akt.[Key]
ORDER BY [Rank] DESC

Note that I simplified your schema for this example.

Conqueror

Forgive me if I'm wrong, but won't the above mean that results will only be shown for the Author table if the an AuthorID also happens to be found in one of the row results for Books? – Zsigmondy

That's true. If it's the case that authors appear who have no books, you would need to adjust the joins accordingly. – Conqueror

Not sure how feasible design some algorithm to calculate the merged rank is. Documentation says: "The rank values indicate only a relative order of relevance of the rows in the result set, with a lower value indicating lower relevance. The actual values are unimportant and typically differ each time the query is run." You'd have to normalize the rank formula (OKAPI BM25) which seems dubious given that absolute rank values will change with the underlying index stats. – Siloa

I don't think this solution works when searching for authors. If you search for "Hemmingway", for instance, then everything up through the first left join returns zero records because there are no books of that title. Thereafter the LEFT JOIN to Author will have nothing to join with, so even though FREETEXTTABLE(Author... returns Earnest Hemmingway, the overall query will return nothing. I feel like this requires a UNION like in @Alberto's solution. What am I missing? – Photic

Answer 2

I had the same problem as you but it actually involved 10 tables (a Users table and several others for information)

I did my first query using FREETEXT in the WHERE clause for each table but the query was taking far too long.

I then saw several replies about using FREETEXTTABLE instead and checking for not nulls values in the key column for each table, but that took also to long to execute.

I fixed it by using a combination of FREETEXTTABLE and UNION selects:

SELECT Users.* FROM Users INNER JOIN
(SELECT Users.UserId FROM Users INNER JOIN FREETEXTTABLE(Users, (column1, column2), @variableWithSearchTerm) UsersFT ON Users.UserId = UsersFT.key
UNION
SELECT Table1.UserId FROM Table1 INNER JOIN FREETEXTTABLE(Table1, TextColumn, @variableWithSearchTerm) Table1FT ON Table1.UserId = Table1FT.key
UNION
SELECT Table2.UserId FROM Table2 INNER JOIN FREETEXTTABLE(Table2, TextColumn, @variableWithSearchTerm) Table2FT ON Table2.UserId = Table2FT.key
... --same for all tables
) fts ON Users.UserId = fts.UserId

This proved to be incredibly much faster.

I hope it helps.

Jean

What happens if your @variableWithSearchTerm contains several terms which exists separately in different tables? Say, " User1 Table1Content Table2Content". Each word exists in correspondent table Users, Table1 and Table 2 only. In this case the query returns no result because all 3 words together doesn't exist in any of 3 tables. – Sparse

I guess in that case you can just use the OR operator – Jean

Answer 3

I don't think the accepted answer will solve the problem. If you try to find all the books from a certain author and, therefore, use the author's name (or part of it) as the search criteria, the only books returned by the query will be those which have the search criteria in its own name.

The only way I see around this problem is to replicate the Author's columns that you wish to search by in the Book table and index those columns (or column since it would probably be smart to store the author's relevant information in an XML column in the Book table).

Acetaldehyde

Answer 4

FWIW, in a similar situation our DBA created DML triggers to maintain a dedicated full-text search table. It was not possible to use a materialized view because of its many restrictions.

Charlot

This is the only way to accomplish specifically what the OP asks. I came here in search for an alternative and don't see one after much research. – Purport

Answer 5

I would use a stored procedure. The full text method or whatever returns a rank which you can sort by. I am not sure how they will be weighted against eachother, but I'm sure you could tinker for awhile and figure it out. For example:

Select SearchResults.key, SearchResults.rank From FREETEXTTABLE(myColumn, *, @searchString) as SearchResults Order By SearchResults.rank Desc

Homogenesis

Answer 6

This answer is well overdue, but one way to do this if you cannot modify primary tables is to create a new table with the search parameters added to one column.

Then create a full text index on that column and query that column.

Example

SELECT 
    FT_TBL.[EANHotelID]                 AS HotelID, 
    ISNULL(FT_TBL.[Name],'-')           AS HotelName,
    ISNULL(FT_TBL.[Address1],'-')       AS HotelAddress,
    ISNULL(FT_TBL.[City],'-')           AS HotelCity,
    ISNULL(FT_TBL.[StateProvince],'-')  AS HotelCountyState,
    ISNULL(FT_TBL.[PostalCode],'-')     AS HotelPostZipCode,
    ISNULL(FT_TBL.[Latitude],0.00)      AS HotelLatitude,
    ISNULL(FT_TBL.[Longitude],0.00)     AS HotelLongitude,
    ISNULL(FT_TBL.[CheckInTime],'-')    AS HotelCheckinTime,
    ISNULL(FT_TBL.[CheckOutTime],'-')   AS HotelCheckOutTime,
    ISNULL(b.[CountryName],'-')         AS HotelCountry,
    ISNULL(c.PropertyDescription,'-')   AS HotelDescription,
    KEY_TBL.RANK 

    FROM [EAN].[dbo].[tblactivepropertylist] AS FT_TBL INNER JOIN
     CONTAINSTABLE ([EAN].[dbo].[tblEanFullTextSearch], FullTextSearchColumn, @s)
      AS KEY_TBL
    ON FT_TBL.EANHotelID = KEY_TBL.[KEY]
    INNER JOIN [EAN].[dbo].[tblCountrylist] b
    ON FT_TBL.Country = b.CountryCode
    INNER JOIN [EAN].[dbo].[tblPropertyDescriptionList] c
    ON FT_TBL.[EANHotelID] = c.EANHotelID

In the code above [EAN].[dbo].[tblEanFullTextSearch], FullTextSearchColumn is the new table and column with the fields added, you can now do a query on the new table with joins to the table you want to display the data from.

Hope this helps

Footing