 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / January 2007not understanding multi-table freetexttable queries
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 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 --- http://milambda.blogspot.com/ > 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? 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 --- http://milambda.blogspot.com/ > One possible way: > [quoted text clipped - 7 lines] > 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? 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 --- http://milambda.blogspot.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. Here is an example http://groups.google.com/group/microsoft.public.sqlserver.fulltext/msg/c4c4f6b91 b50ed44?dmode=source  SignatureHilary 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 >> 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. 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 --- http://milambda.blogspot.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 >> Make sure you use a sufficient amount of representative data to >> verify the result. Perhaps you could also share your final solution [quoted text clipped - 20 lines] > > HTH! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.