Why doesn't FTS match any records?

I created an indexed view with a few fields that i created a full text search based on these fields.

here is my view:

alter VIEW vFullTextSearch
WITH SCHEMABINDING
AS
    SELECT  M.Id as MessageId, M.Subject , M.Body,MR.EmailAddress, MR.FriendlyName,
            M.Subject + ' ' + M.Body + ' ' + MR.EmailAddress + ' ' + MR.FriendlyName as FTS
    FROM DBO.MESSAGE M
        INNER JOIN DBO.MessageRecipient MR ON M.Id = MR.MessageId AND MR.RecipientTypeId=1 
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1_vFTS
    ON vFullTextSearch (MessageId);
GO

I indexed all the fields of the table.

I am using this SQL to find results

SELECT M.*
FROM Message AS M
    INNER JOIN FREETEXTTABLE(vFullTextSearch, *, 'Doe') AS KEY_TBL
        ON M.Id = KEY_TBL.[KEY];

but with "John Doe" being the [vFullTextSearch.FriendlyName] value in all the records of my view why am i not gettign results when searching all the fields for 'doe' ?

According to MSDN...http://msdn.microsoft.com/en-us/library/ms177652.aspx

table
Is the name of the table that has been marked for full-text querying. table or view can be a one-, two-, or three-part database object name. When querying a view, only one full-text indexed base table can be involved.
table cannot specify a server name and cannot be used in queries against linked servers.

Since your view has full text indexes on more than one table you will not be allowed to do this.

You could possibly use a union to check both tables in your view.

SELECT {your select columns}
FROM Message AS M
    INNER JOIN FREETEXTTABLE(dbo.Message, {your message columns}, 'Doe') AS KEY_TBL
        ON M.Id = KEY_TBL.[KEY]
    INNER JOIN MessageRecipient MR on M.Id = MR.MessageId AND MR.RecipientTypeId=1

Union

SELECT {your select columns}
FROM Message AS M
    INNER JOIN MessageRecipients MR on  M.Id = MR.MessageId AND MR.RecipientTypeId=1
    INNER JOIN FREETEXTTABLE(MR.MessageRecipients, {your MessageRecipients columns}, 'Doe') AS KEY_TBL
        ON MR.Id = KEY_TBL.[KEY];

I don't have access to a SQL installation right now, but let me know if it works.

StackExchange Database Administrators Q#2700, answer score: 4