Selecting from combined fields across tables

Question

Selecting from combined fields across tables

mustang

Grasshopper

Points: 14
More actions
November 16, 2008 at 1:04 pm

#196082

Here's the setup (I'll greatly simplify tables to make it easy):
Category table, which can nest:
CategoryID
ParentCategoryID
CategoryName
CategoryKeywords
Item table:
ItemID
ItemName
ItemKeywords
ItemCat pivot table:
ItemCatID
ItemID
CategoryID
Search 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.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323755 More actions · Answer 1

you'll be best served if you do this with a full text search.

Believe it or not, it's not that difficult, and there was a recent article here on SSC on how to make your own "Google-like " search engine.

I grabbed a sample of my own database and had it up and running before lunch.

read the article here:

http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/

fulltext searches are what allows you to do the best similar searches, so when you search for "Ford Keychains", exact matches rate highest, then things related to "Ford" And "key", then single word matches, etc etc.

fulltest does things you cannot do with the "LIKE" statement, like the core of a word, removing verb tenses like -ing -ed -ous etc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mustang Grasshopper Points: 14 More actions · Answer 2

Thanks for the link, I will follow up. However the behavior of linking to similar items becoming less relevant is exactly what I don't want to do, there are potentially hundreds or thousands of items that would come up as very similar. Can it be setup to search for literal terms only and exclude anything else?