Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-22 : 21:52:56
|
Aquarius writes "So, we've got a SQL Server 7 database. There's the concept of a virtual object, the attributes of which are spread across two or three tables. The links between these tables are defined in a link table. Some of the attributes are plain text. I need to be able to set up a search engine over these text attributes.
So, to clarify, an object may have, say, attributes called text1, text2, text3, and text4. Text1 is stored in table TABLE1. Text2 and text3 are stored in table TABLE2. Text4 is stored in table TABLE3. TABLE1 and TABLE2 are keyed on object ID and can be joined that way. TABLE2 and TABLE3 are linked through table LINK (which maps TABLE2 keys to TABLE3 keys).
How can I take a search request of the type used in search engines (say, "oranges and apples or bananas") and return an object that matches? (read: return the object ID from TABLE1) The key thing here is that seeing whether "oranges and apples or bananas" matches text1 is easy. But if "oranges" is in text1 and "bananas" is in text4 then the object still has to match, and I can't think of a good way of doing it.
Ways I have thought of:
* Define a view table called "object" that has one large text field in it that is actually the concatenation of text1, text2, text3 and text4, and then make it full-text searchable, and use contains() on it -- this is difficult because of the dynamic link to text4 and additionally because text4 is not one field, it's actually lots of varchar(255)s in TABLE3 which you add together to get text4. I'm not convinced that you can enable full-text searching on a table that's really a view, anyway.
* Run a nightly process that selects text1, text2, text3, and text4 from each object and dumps them to a flatfile, and then uses Index Server to index the flatfiles -- we can then call Index Server to do the search, get its results, and map each file that it returns as a hit to an object in the database
* Buy in some third-party solution that knows how to do this -- I'm having difficulty finding one, though.
* Something else I haven't thought of yet
Oh, by the way, did I mention that text3 might actually be a PDF, and that needs to be searchable too? :-)
I'm not convinced that SQLTeam is the best place to ask this, because it may not be a SQL question at all (if I end up exporting to flatfiles and using Index Server, for instance), but I'd love it if it were possible to do this in SQL." |
|
|
|
|
|
|
|
Searching for records that are distributed over multiple fields in multiple tables