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.
How can I sort by total field? I mean, I am talking about Interactive sort, and I want to sort not by the single field but by total filed of the group (not the general total but the partial total(*)).