CODEWITHSUNDEEP
sqlfull-text-search
miladrasooli
miladrasooli

Reputation: 115

Full Text Search on multi table and multi columns

I want to perform a SQL Server full-text search on the following query :

select distinct 
    Products.ProductID, Products.ProductCode, Products.PTitleF, 
    STUFF((SELECT distinct 
               ', ' + Author.AuFirstName + N'  ' + Author.AuLastName + N' '+
    ISNULL(Translator.FirstName,N' ')+N' '+ISNULL(Translator.LastName,N' 
  ')+N' '+p.PTitleF+N' '+ISNULL(Publishers.PName,N' ')
           FROM 
               Publishers 
           INNER JOIN  ProductPublisher ON 
Publishers.PublisherID = ProductPublisher.PublisherID 
           RIGHT OUTER JOIN
               ProductTranslator 
           INNER JOIN Translator ON 
ProductTranslator.TranslatorID = Translator.TranslatorID 
           RIGHT OUTER JOIN
               Products AS p ON 
ProductTranslator.ProductID = p.ProductID ON
ProductPublisher.ProductID = p.ProductID 
           LEFT OUTER JOIN
               ProductAuthor 
           INNER JOIN
               Author ON 
ProductAuthor.AuthorID = Author.AuthorID ON 
p.ProductID = ProductAuthor.ProductID
           WHERE
               Products.ProductID = p.ProductID     
           FOR XML PATH ('')), 1, 1, '') Authors
from 
    Products;

As you can see I want to merge some fields from multiple tables into one record to perform a full-text search on them.

What is the best method to accomplish my goal here?

Thank you.

Upvotes: 0

Views: 1143

Answers (1)

Dan Sorensen
Dan Sorensen

Reputation: 11763

Before you get to your search, run the SELECT part of your query above and make sure all the data you're looking for is available in the raw output. With that many inner and outer joins, you may be eliminating the rows you're looking for. It is a good initial check before you get further.

Secondly, I would favor a view of those columns rather than STUFF. Of course it depends on your data and needs, but I would try a view over all of those joined columns first.

Next make sure Full Text Search is installed and configured:

  1. Make sure you have full-text search installed on your SQL Server. It is not installed by default, and not available on SQL Express. (you didn't specify your version)

  2. Create a full-text search catalog to store your full text index data.

    CREATE FULLTEXT CATALOG foo;

If this is the only full text catalog, you can specify it as the default:

CREATE FULLTEXT CATALOG foo AS DEFAULT;

  1. Add some full-text indexes for the tables/columns you are looking for to your full-text search catalog. You set these indexes up one table at a time.

    CREATE FULLTEXT INDEX ON dbo.Author ( AuFirstName LANGUAGE 1033, AuLastName LANGUAGE 1033 ) KEY INDEX [PK_dbo.Author] ON foo;

1033 indicates that the full text search should use the English dictionary, if this is not correct, you'll need to lookup the number for your desired language.

The full text indexed table needs to know the table's primary key. I guessed that the primary key (PK) for that table is called [PK_dbo.Author]. Make sure you replace [PK_dbo.Author] with the correct PK for the Author table.

You are not ready to search your data with a full text SQL Query using the CONTAINS or FREETEXT keywords.

Upvotes: 1

Related Questions