tl;dr version: A full-text search using FREETEXTABLE that worked fine on one column in one table produces 100% false postitives and 100% false negatives when the same steps are followed but the full-text index is on several columns in a view made of two base tables (the original table and a new one). Research reveals contradictory claims that a full-text search can be run against multiple tables if they are joined in a view, and that the search can only involve a single base table when a view is queried.
We are running SQL Server 2008 R2. As part of a larger project, we are creating a searchable database of recipes for a customer. The customer provided data in XML format purchased from a third party. Based on the wireframe of the application, we imported the data and what data was relevant. DDL in pertinent part and a subset of the data:
I was not told what the search criteria would be, but assumed more than one field and that full-text would yield results more in tune with what the customer was looking for. DDL for FTC and full-text index:
I wrote the following stored procedure (optional but useful for testing):
This worked as advertised. A search on 'chili-rubbed salsa' would return about a dozen recipes, with "Chili-Rubbed Steaks & Pan Salsa" as the top recipe and others with decreasing relevance down to "Paprika-Herb Rubbed Chicken". Just from eyeballing the titles, there appeared to be no false positives.
I updated the project manager to let him know that recipes were searchable on title and if the customer wanted any other fields included, I could add them in. He then asked why we weren't searching on cuisine type, theme, health considerations, and others. Ugh. We weren't told to import that data. Back to square one.
The other data wouldn't fit into the recipe table without denormalizing it, so we imported it into a new table. Entire DDL and a subset of data:
Then, having read that full-text search can only work on one table, and that to search multiple tables one must create a view, I did so:
And altered the SP (EDIT: I have updated the code below to indicate the actual changes I had made):
And now the results of the search are complete nonsense. A search on "salsa", for example, finds five unique recipes, none of which contains the word "salsa" or any reasonable thesaurus synonym in any of the indexed fields; none of the types of dishes one associates with salsa are found. (One of the hits does contain the word "sauce", which I suppose might be a thesaurus synonym for "salsa".) Two of the five recipes are the first two recipes in the dataset above. Meanwhile neither salsa-roasted salmon, nor any other recipe which contains the word "salsa", is returned by the search. I have verified that the full-text catalog and index are both created as expected, and that the full-text catalog has the same number of items in it as the number of rows returned from the view (about 4000, even though it reports being 0 MB in size).
An additional annoyance: If a recipe meets the criteria, then ALL of its records in the view are selected. It's almost as though it is always one of the columns in the table recipe that leads to the hit, so all the joined records in recipe_search are included.
In looking into this bizarre reversal, I came across the following link: "When querying a view, only one full-text indexed base table can be involved."
Question #1: Which is correct--can you, or can you not, query multiple tables in a full-text search?
Question #2: Have I left something out in trying to create a multi-table full-text search, and if not, what might be the issue here?