| Groups | Blog | Home |
sql server full text search : not understanding multi-table freetexttable queries
Look at the FREETEXTTABLE invocation in your query: you're only issuing a
full-text query against the products table, not the manufacturers table. You need to also reference the manufacturers table (in a separate call to the fts function). ML ---
One possible way:
select <columns> from table1 inner join freetexttable(table1,*, @srchstring) ft1 on ft1.key = table1.<key column> inner join table2 on table2.<common key> = table1.<common key> inner join freetexttable(table2,*, @srchstring) ft2 on ft2.key = table2.<key column> ML ---
Of course it's possible. The question is how to combine the two ranks.
Perhaps someone else out there has dealt with such questions in the past. Unfortunately, I have never used the FTS this way. ML ---
Hi: I have a FT cat with two tables in it:
products, manufacturers and the tables each have a field in the FT Cat: "pname", "mname" each row in "products" has a field "mid" which corresponds to the same field in "manufacturers" when I run a query like: SELECT p.pname, p.p_id, m.mname from products p INNER JOIN manufacturers m ON p.mid = m.mid INNER JOIN (SELECT Rank, [KEY] FROM FREETEXTTABLE(products,*, 'giant')) AS k ON k.[key]=p.p_id ORDER BY Rank DESC This query returns results only if the search term "giant" is in the products/pname field, but not in manufacturers/mname field. What do I need to do if I want the query to return results from all the tables in the FT Cat? TIA
Make sure you use a sufficient amount of representative data to verify the
result. Perhaps you could also share your final solution with us...? ML ---
[quoted text, click to view]
> Look at the FREETEXTTABLE invocation in your query: you're only issuing a > full-text query against the products table, not the manufacturers table. > > You need to also reference the manufacturers table (in a separate call to > the fts function). I figured as much...can you give me an example of how to do that?
-- [quoted text, click to view] "ML" <ML@discussions.microsoft.com> wrote in message news:61725666-82C6-4A03-AB36-F18715352854@microsoft.com... > One possible way: > > select <columns> > from table1 > inner join freetexttable(table1,*, @srchstring) ft1 > on ft1.key = table1.<key column> > inner join table2 > on table2.<common key> = table1.<common key> > inner join freetexttable(table2,*, @srchstring) > ft2 > on ft2.key = table2.<key column> Thanks for that...in my original example, I realize I left something out... I was also returning the rank from the freetext query and using that to return weighted results: SELECT p.pname, p.p_id, m.mname, k.rank from products p INNER JOIN manufacturers m ON p.mid = m.mid INNER JOIN (SELECT Rank, [KEY] FROM FREETEXTTABLE(products,*, 'giant')) AS k ON k.[key]=p.p_id ORDER BY Rank DESC Is that still possible when querying two tables in the freetext query separately?
[quoted text, click to view]
"ML" <ML@discussions.microsoft.com> wrote in message news:6F732F7E-BB2D-4A8A-B373-0C91A9EC3036@microsoft.com... > Of course it's possible. The question is how to combine the two ranks. > Perhaps someone else out there has dealt with such questions in the past. > Unfortunately, I have never used the FTS this way. Yeah, me neither...I'm hoping someone on this list can tell me.
I was able to get it to work by using a UNION between the two queries, just
changing the table in the freetext query. I don't know if this is the best or preferred way to do it, though.
Here is an example
http://groups.google.com/group/microsoft.public.sqlserver.fulltext/msg/c4c4f6b91b50ed44?dmode=source -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "geek-y-guy" <noone@nowhere.org> wrote in message news:O339T8XQHHA.3440@TK2MSFTNGP03.phx.gbl... > "ML" <ML@discussions.microsoft.com> wrote in message > news:6F732F7E-BB2D-4A8A-B373-0C91A9EC3036@microsoft.com... >> Of course it's possible. The question is how to combine the two ranks. >> Perhaps someone else out there has dealt with such questions in the past. >> Unfortunately, I have never used the FTS this way. > > Yeah, me neither...I'm hoping someone on this list can tell me. >
[quoted text, click to view]
"ML" <ML@discussions.microsoft.com> wrote in message news:507C00FE-6A48-43FA-8D45-65DF571D1C5E@microsoft.com... > Make sure you use a sufficient amount of representative data to verify the > result. Perhaps you could also share your final solution with us...? Sure I'll share! The following works great, but I still don't know if it's the best way to do it or not: SELECT p.pname, p.p_id, m.mname, k.rank from products p INNER JOIN manufacturers m ON p.mid = m.mid INNER JOIN (SELECT Rank, [KEY] FROM FREETEXTTABLE(products,*, 'giant')) AS k ON k.[key]=p.p_id UNION SELECT p.pname, p.p_id, m.mname, k.rank from products p INNER JOIN manufacturers m ON p.mid = m.mid INNER JOIN (SELECT Rank, [KEY] FROM FREETEXTTABLE(manufacturers,*, 'giant')) AS k ON k.[key]=m.m_id ORDER BY Rank DESC I actually stepped this out to a third table in the full-text cat with a second UNION SELECT, and it worked perfectly. HTH!
Hello geek-y-guy,
You need to decide how you want to rank the data. Does a match in products beat a match in manufacturers, Does a poor match in both products and manufacturers beat a good match in products. Essentially the 2 ranks you get back are not related i.e. a rank of 80 for the products frretext will not mean the same as a rank of 80 in the manfucaturers freetext. Your code may give duplicates, the best option is to either add the 2 ranks (sort of even weighting) or mulitply one by a factor i.e. 100 and then add them, This weights a mathc in the owe table before the other. We do exactly that, if a search for a job is matched in the job title it gets ranked above one that matches in the job description. So look at ML's second post and then do what you will with the ranks Simon Sabin SQL Server MVP http://sqlblogcasts.com/blogs/simons [quoted text, click to view] > "ML" <ML@discussions.microsoft.com> wrote in message > news:507C00FE-6A48-43FA-8D45-65DF571D1C5E@microsoft.com... > >> Make sure you use a sufficient amount of representative data to >> verify the result. Perhaps you could also share your final solution >> with us...? >> > Sure I'll share! > > The following works great, but I still don't know if it's the best way > to do it or not: > > SELECT p.pname, p.p_id, m.mname, k.rank from products p > INNER JOIN manufacturers m ON p.mid = m.mid > INNER JOIN (SELECT Rank, [KEY] > FROM FREETEXTTABLE(products,*, 'giant')) AS k > ON k.[key]=p.p_id > UNION SELECT p.pname, p.p_id, m.mname, k.rank from products p > INNER JOIN manufacturers m ON p.mid = m.mid > INNER JOIN (SELECT Rank, [KEY] > FROM FREETEXTTABLE(manufacturers,*, 'giant')) AS k > ON k.[key]=m.m_id > ORDER BY Rank DESC > I actually stepped this out to a third table in the full-text cat with > a second UNION SELECT, and it worked perfectly. > > HTH! >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |