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

by

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.

More Related Contents:

  1. How to split a comma-separated value to columns
  2. Using T-SQL, return nth delimited element from a string
  3. How to get a float result by dividing two integer values using T-SQL?
  4. Get first day of week in SQL Server
  5. SQL Server add auto increment primary key to existing table
  6. How to delete large data of table in SQL without log?
  7. Confused about UPDLOCK, HOLDLOCK
  8. Retrieve column definition for stored procedure result set
  9. Change Notification with Sql Server 2008
  10. Do while loop in SQL Server 2008
  11. Query times out when executed from web, but super-fast when executed from SSMS
  12. TSQL: How to convert local time to UTC? (SQL Server 2008)
  13. Regular Expressions in SQL Server servers?
  14. Transpose rows and columns with no aggregate
  15. How to find the port for MS SQL Server 2008?
  16. How to convert number of minutes to hh:mm format in TSQL?
  17. SQL Server Configuration Manager cannot be found
  18. SQL Server recursive query
  19. Temporarily disable all foreign key constraints
  20. How do I move a table into a schema in T-SQL
  21. nvarchar(max) vs NText
  22. How to display two digits after decimal point in SQL Server
  23. How to wait for 2 seconds?
  24. What are row, page and table locks? And when they are acquired?
  25. 7645 Null or empty full-text predicate
  26. Find broken objects in SQL Server
  27. Error converting data types when importing from Excel to SQL Server 2008
  28. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?
  29. SQL Server 2008 Spatial: find a point in polygon
  30. Implementing one-to-zero-or-one relation in SQL Server

Leave a Comment