I am trying to query multiple columns in multiple tables. I need to weigh certain columns in the results. My current code looks as follows:
"select Key_Tbl.[key], tblBlogThreads.BlogThreadNum, tblBlogThreads.Body,tblBlogThreads.PublishDate, tblBlogThreads.Title, tblBlogThreads.Teaser, tblBlogThreads.MetaKeywords, [rank] from freetexttable(tblBlogThreads, body, '" & tstr & "' ) AS Key_Tbl INNER JOIN tblBlogThreads ON tblBlogThreads.BlogthreadID = Key_Tbl.[key] order by [rank] desc"
The problem is i need to search in columns tblBlogThreads.Title, tblBlogThreads, Keywords, and tblBlogThreads.Body, and weigh them in that order.
The ranks returned are 1-1000, therefore match by title will have high rank, then keywords, then body. I forgot to trim duplicates. this one fixes it
select
Key_Tbl.[key], tblBlogThreads.BlogThreadNum,
tblBlogThreads.Body,tblBlogThreads.PublishDate,
tblBlogThreads.Title,
tblBlogThreads.Teaser,
tblBlogThreads.MetaKeywords,
[rank]
from
(select [key], max(rank) as rank from
(select [key], rank + 2000 as rank from freetexttable(tblBlogThreads, title, '" & tstr & "' )
union all
select [key], rank + 1000 from freetexttable(tblBlogThreads, keywords, '" & tstr & "' )
union all
select [key], rank from freetexttable(tblBlogThreads, body, '" & tstr & "' )
) tbl1
group by [key]) AS Key_Tbl
INNER JOIN tblBlogThreads ON tblBlogThreads.BlogthreadID = Key_Tbl.[key]
order by [rank] desc
0
DidiahProdAuthor Commented:
Thanks!
these look good but there's a syntax error in there somewhere- getting error near a space, near a space and near a ).
Looking into it...
0
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
create procedure myproc ( @tsq varchar(100) )
as
select
Key_Tbl.[key], tblBlogThreads.BlogThreadNum,
tblBlogThreads.Body,tblBlogThreads.PublishDate,
tblBlogThreads.Title,
tblBlogThreads.Teaser,
tblBlogThreads.MetaKeywords,
[rank]
from
(select [key], max(rank) as rank from
(select [key], rank + 2000 as rank from freetexttable(tblBlogThreads, title, @tsq )
union all
select [key], rank + 1000 from freetexttable(tblBlogThreads, keywords, @tsq )
union all
select [key], rank from freetexttable(tblBlogThreads, body, @tsq )
) tbl1
group by [key]) AS Key_Tbl
INNER JOIN tblBlogThreads ON tblBlogThreads.BlogthreadID = Key_Tbl.[key]
order by [rank] desc
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Key_Tbl.[key], tblBlogThreads.BlogThreadN
tblBlogThreads.Body,tblBlo
tblBlogThreads.Title,
tblBlogThreads.Teaser,
tblBlogThreads.MetaKeyword
[rank]
from
(select [key], rank + 2000 as rank from freetexttable(tblBlogThrea
union all
select [key], rank + 1000 from freetexttable(tblBlogThrea
union all
select [key], rank from freetexttable(tblBlogThrea
) AS Key_Tbl
INNER JOIN tblBlogThreads ON tblBlogThreads.BlogthreadI
order by [rank] desc