How do I create a fulltext index on a view?

[afroblanca]

Hello all,

I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :

SELECT ci.itemName
FROM Content_Items ci
INNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemId
INNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]

However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.

So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.

It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error :
A unique column must be defined on this table/view.

How do I create a unique column within a view?

Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.

 

[ca8msm]

I don't think ASP.NET will be able to help you.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]