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 |
|
WabbaStang
Starting Member
4 Posts |
 Posted - 2008-11-16 : 15:03:32
|
| Here's the setup (I'll greatly simplify tables to make it easy):Category table, which can nest:CategoryIDParentCategoryIDCategoryNameCategoryKeywordsItem table:ItemIDItemNameItemKeywordsItemCat pivot table:ItemCatIDItemIDCategoryIDSearch terms are generally 3-4 words, such as "keychain ford leather" for example. Not all of these terms will be found in one table, it's likely a combination. In this case the category name might be "Ford Keychains" and an item name might be "Leather". The basic idea is to find all search terms in a combination of fields across tables. The pivot table makes it slightly more fun but not a major obstacle. The Keywords field in both Item and Category tables can contain alternate item/cat names that need to be searched but are not displayed (ex. keychain, keytag, key fob).Right now I have a big ugly blob of code that takes the list of fields to search (let's say Category.CategoryName,Category.CategoryKeywords,Item.ItemName,Item.ItemKeywords) and the search term and breaks it down into individual words, and builds a massive WHERE clause, looping ANDs on the search words and ORs on the fields. It works, it's huge, it's ugly, it's inefficient. Throw in the pivot table and do this across multiple categories, as well as about 4 other JOINs to some other tables searched on columns other than the search terms (Model,ModelYear etc) and it's enormous.What essentially needs to be done is "WHERE table.field + table.field + table.field LIKE 'this' AND 'that' AND 'whatever'" - Of course this would be too easy.Is there an obvious way to be doing this more efficiently? No it's not indexed; the database is constantly updating and i really dont know much about full text indexing or have time to, so I'm just looking for ways to more efficiently structure the query. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-16 : 22:57:14
|
| Duplicate. please dont cross posthttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114547 |
 |
|
|
|
|
|
|
|
Selecting from combined columns across tables