Free Text Search across multiple tables

Anyone have any recommendations on how to bring together a search across multiple tables? Like I'd love to do a search where i searched for the product name, company name or tag all in one query (though all are in separate tables)... is that possible? Individually, it's easy, but combined it's very hard.You can join the tables and use like, but I should probably first take a look at the Free text search engine (but it depends on how the table looks like, how the search will be formed in the future, how much data the table has etc), take a look at the following site:http://www.databasejournal.com/features/mssql/article.php/3441981|||

actually, that's just it... i'm using the full text search engine, which generates catalogs per table. I'm not sure how to bring the table results together. so, for example:

Declare @.search nvarchar(100)select @.search ='apple'SELECT TOP 5 * FROM brands INNER JOIN (SELECT * from freetexttable (Brands,*,@.Search) ORDER BY [RANK] DESC) AS search ON brands.brandid = search.[KEY]SELECT TOP 5 * FROM products INNER JOIN (SELECT * from freetexttable (products,*,@.Search) ORDER BY [RANK] DESC) AS search ON products.productid = search.[KEY]SELECT TOP 5 * FROM companies INNER JOIN (SELECT * from freetexttable (companies,*,@.Search) ORDER BY [RANK] DESC) AS search ON companies.companyid = search.[KEY]

 How do i bring these tables together and order them according to the closest match across the tables?

|||

Hey,

Did you have any luck with this? I'm using SQL 2005, and I've done a bit of testing, but haven't got the expected results!

Thanks