Help: UNION vs. CONTAINSTABLE

I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns.

Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank.

I'm not sure if this is possible. So far, I have:

1SELECT RANK, field_1, field_2, ..., field_mFROM2(3SELECT RANK, field_1, field_2, ..., field_mFROM T_1,4CONTAINSTABLE(T_1, field_i,@.searchText) searchTable5WHERE KEY = T_1.field_i6UNION7SELECT RANK, field_1, field_2, ..., field_mFROM T_2,8CONTAINSTABLE(T_2, field_i,@.searchText) searchTable9WHERE KEY = T_2.field_i10UNION11.12.13.14UNION15SELECT RANK, field_1, field_2, ... field_mFROM T_n,16CONTAINSTABLE(T_n, field_i,@.searchText) searchTable17WHERE KEY = T_n.field_i18)19ORDER BY RANKDESC

I haven't tried it yet, but it seems wrong. How do we actually do this?

You should first create a CTE (Common Table Expression) or a VIEW:http://www.singingeels.com/Articles/Understanding_SQL_Complex_Queries.aspx

You would do something like this:

WITH MyTables (ColA, ColB etc...) AS ( SELECT * FROM T_1 UNION SELECT * FROM T_2)

now.. you do your query with "MyTables" as the table name...

|||

I'm now getting an error that says:

Cannot use a CONTAINS or FREETEXT predicate on object 'MyTables' because it is not full-text indexed.

That's true, though. The table we created isn't full-text enabled.

----------

My other option is to do a query for each table I want searched, then merge them all into a DataSet, then reorder the DataSet by rank. How tedious, though, especially if I want pagination of large tables.

|||

I read somewhere that SQL Server 2005 does not support free text searching over multiple tables. There's something called SQLONE that actually allows FTS over multiple tables.