October 9, 2012 at 9:13 am
Hi everyone
I have a fulltext search that brings up keyword results, what I am after though is to base the results on the column search. I want to give certain columns more weight in the search, if that makes sense.
I have come up with the following, but want to pick your brains before I implement it
SELECT *
FROM Product
INNER JOIN FreeTextTable(Product, [UnitDescription], @searchText) AS unitDescriptionRank
ON unitDescriptionRank. = Product.ProductID
INNER JOIN FreeTextTable(ProductSupplier, [SupplierName], @searchText) AS productSupplierRank
ON productSupplierRank. = ProductSupplier.ProductSupplierID
INNER JOIN FreeTextTable(ProductManufacturer, [ManufacturerName], @searchText) AS productManufacturerRank
ON productManufacturerRank. = ProductManufacturer.ProductManufacturerID
ORDER BY productSupplierRank.Rank, unitDescriptionRank, productManufacturerRank
As you can see, I want to search for a keyword, that will search my UnitDescription, SupplierName and ManufacturerName, but I want the results to be ordered by the priority of each column.
Im not sure if I am explaining myself correctly. Hope you can understand what I mean.
October 9, 2012 at 9:31 am
[EDIT]
Ok, after looking at the queries I noticed that its not going to work as the search string needs to be present in all 3 tables.
How would I go about solving this then? I can create a search function that will basically do the following:
1. create a #table to store temporary search results
2. populate #table with priority 1 query results multiplying the rank by 100
3. populate #table with priority 2 query results multiplying the rank by 50
etc.
4. return #table ordering by rank DESC
Or am I looking at this from the wrong perspective?
October 9, 2012 at 9:36 am
if you change form inner join to left outer joins, won't you get the desired results, where the string is in one or more tables??
Lowell
October 9, 2012 at 9:42 am
the problem with the left join option is that if its not in any of the tables, it will bring back all the records of the main product table, which I don't want.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply