SQL Server Free Text search: search words from a phrase in two tables

I have a table with companies and one with categories. I'm using SQL Server Free Text search, and searching companies (by name and description) works fine. But now I also want to include the category table.

I want to search for something like: ABC 24 Supermarket.

Now, ABC 24 should make a match with the Name column in the company table, and Supermarket is the name of the category this company is connected to.

Right now I have something like this:

DECLARE @SearchString VARCHAR(100) = '"ABC 24 Supermarket"'
SELECT * FROM Company CO
INNER JOIN Category CA
ON CA.CategoryId = CO.CategoryId
WHERE CONTAINS((CO.[Description], CO.[Name]), @SearchString)
AND CONTAINS(CA.[Description], @SearchString)

But this of course, gives me nothing, because my search string cannot be found in either the company or the category table. Does anyone have an idea on how to do a combined search on my company and category table?

The idea of splitting strings, as suggested in Lobo's answer below, is not really an option. Because i don't know which part will be the one that should match a category and which part should be used for matching company names/descriptions. Users might just as well type in "Supermarket ABC 24".

Solution

Imho the right way to do this is to create an indexed view containing the primary key of the main table ('company' in your example) and a second column containing all the stuff you're actually want to search, i.e.

create View View_FreeTextHelper with schemabinding as
select CO.PrimaryKey,                            -- or whatever your PK is named
       CO.description +' '+CA.description +' '+CO.whatever as Searchtext
  from dbo.company CO join 
       dbo.category CA on CA.CategoryId = CO.CategoryId

Note the two-part form of your tables. A few restrictions arise from this, e.g. all involved tables must be in the same table space and as far as I remember, no TEXT columns are allowed in this kind of concatenation (you may cast them though).

Now create a unique index on the PrimaryKey column

create unique clustered index [View_Index] 
    on View_FreeTextHelper (PrimaryKey ASC)

Finally create the fulltext index on the view using the 'Searchtext' column as the only column to index. Of course, you may add more columns, if you e.g. wish to distinguish in searching for company name and location and, the names of the managers (you would just concatenate them in a second column).

Retrieving your data is now easy:

select tbl.RANK,
       co.* 
  from freetextTable(View_FreeTextHelper,Search,'Your searchtext here') tbl
  join company co on tbl.key=co.PrimaryKey
 order by tbl.RANK desc

You may also limit the output using select top 50 as the freetexttable clause will eventually return quite a lot of close and not so close results.

And finally, don't get confused if you cannot find thing like 'off the shelf inc.' Beware of the stop lists. These are list of words which are very common, have no semantic use (like the) and are therefore removed from the text to be indexed. In order to include them, you have to switch of the stoplist feature.

A last tipp: full text is very powerful but has a lot of features, tricks and caveats. It takes quite a bit to fully understand the techniques and get the best results you want.

Have fun.