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.
Search Freetext Multiple Columns SQL2000
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.BlogThreadN
The problem is i need to search in columns tblBlogThreads.Title, tblBlogThreads, Keywords, and tblBlogThreads.Body, and weigh them in that order.
"select Key_Tbl.[key], tblBlogThreads.BlogThreadN
The problem is i need to search in columns tblBlogThreads.Title, tblBlogThreads, Keywords, and tblBlogThreads.Body, and weigh them in that order.
Asked:
-
4
-
3
1 Solution
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.BlogThreadN
tblBlogThreads.Body,tblBlo
tblBlogThreads.Title,
tblBlogThreads.Teaser,
tblBlogThreads.MetaKeyword
[rank]
from
(select [key], max(rank) as 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
) tbl1
group by [key]) AS Key_Tbl
INNER JOIN tblBlogThreads ON tblBlogThreads.BlogthreadI
order by [rank] desc
select
Key_Tbl.[key], tblBlogThreads.BlogThreadN
tblBlogThreads.Body,tblBlo
tblBlogThreads.Title,
tblBlogThreads.Teaser,
tblBlogThreads.MetaKeyword
[rank]
from
(select [key], max(rank) as 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
) tbl1
group by [key]) AS Key_Tbl
INNER JOIN tblBlogThreads ON tblBlogThreads.BlogthreadI
order by [rank] desc
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...
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...
That was meant to include a start " and an end " to be assigned to a string from ASP.NET. That's why there are two
" & tstr & "
bits in the middle
It does not run as is and will cause an error from Query Analyzer!
But I think you get the idea.
" & tstr & "
bits in the middle
It does not run as is and will cause an error from Query Analyzer!
But I think you get the idea.
yah thanks- it runs in the QA but won't run in sql- looking more into it. i can't get it to go in an SP either
How would this be represented as a stored procedure where tsq is the parameter (@tsq)
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
create procedure myproc ( @tsq varchar(100) )
as
select
Key_Tbl.[key], tblBlogThreads.BlogThreadN
tblBlogThreads.Body,tblBlo
tblBlogThreads.Title,
tblBlogThreads.Teaser,
tblBlogThreads.MetaKeyword
[rank]
from
(select [key], max(rank) as 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
) tbl1
group by [key]) AS Key_Tbl
INNER JOIN tblBlogThreads ON tblBlogThreads.BlogthreadI
order by [rank] desc
as
select
Key_Tbl.[key], tblBlogThreads.BlogThreadN
tblBlogThreads.Body,tblBlo
tblBlogThreads.Title,
tblBlogThreads.Teaser,
tblBlogThreads.MetaKeyword
[rank]
from
(select [key], max(rank) as 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
) tbl1
group by [key]) AS Key_Tbl
INNER JOIN tblBlogThreads ON tblBlogThreads.BlogthreadI
order by [rank] desc
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
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.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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