dB search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

dB search

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-10-01 : 04:47:33

Hi,

I'm fairly new to SQL Server and have until now been using Access to fulfill my needs. Although I've been working with SQL Server for about a year its only been more recently that I've been using stored procedures etc.

Basically I'm trying to write a simple search page for my users, this needs to search through a few colums and is on SQL Server 7 (web host not upto date yet). I found this great article the other day which was exactly what I wanted [url]http://www.sqlteam.com/item.asp?ItemID=1876[/url] until today when I've decided I want to expand it from a simple "OR" statement to allow the user to search for all the words in the string.

I have been toying with the ideas of counting the number of occurances of an ID etc but can't seem to figure it out so I thought I would turn to you guys. Any ideas or good references for me?

Many thanks in advance.

Tim

SamC
White Water Yakist

3467 Posts

Posted - 2004-10-01 : 05:57:12

DECLARE @Item_Count INT

SET @Item_Count = @Item_Count + 1 -- Count the total search items in the loop

SELECT  TOP 50 S.ItemID, I.Title, 
        DatePosted=Convert(varchar, I.DatePosted, 101), 
        I.URL, Rating=Count(*)
FROM	#SearchResults S,
	vActiveItems I
WHERE	S.ItemID = I.ItemID
Group by S.ItemID, I.Title, I.DatePosted, I.URL
HAVING COUNT(*) = @Item_Count
Order by 5 DESC, 3 DESC

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-10-01 : 07:27:09

Thats great thanks exactly what I was trying to get to, the only thing is that now (my own fault) I'm getting duplicate values, say you're searching for Site Tent in the following:

ID | Name | Thing
------------------------
1 | Site | Site
2 | Some | Tent
3 | Tent | Some

This Temp table will look like:

ItemID
-------
1
1
2
3

(Duplicates for ID "1") So I thought to myself, how can I work around this, so I added the Keywords to the table:

ItemID | Keyword
---------------------
1 | Site
1 | Site
2 | Tent
3 | Tent

My idea was to delete the Duplicate values (before the TOP 50 select) by using a Group but it doesn't seem to work, could this be because its a temporary table? I was using:

delete from #SearchResults
where (#SearchResults.[rowid], #SearchResults.[ItemID], #SearchResults.[Keyword])
not in
(select min(#SearchResults.[rowid]), #SearchResults.[ItemID], #SearchResults.[Keyword] from #SearchResults group by #SearchResults.[ItemID], #SearchResults.[Keyword])

But I keep getting a syntax on the line:
where (#SearchResults.[rowid], #SearchResults.[ItemID], #SearchResults.[Keyword])
so I leave it as:
where (#SearchResults.[rowid])

and the analyser pumps out:
Server: Msg 207, Level 16, State 3, Procedure sp_LEISURE_Keyword_Search, Line 49
Invalid column name 'rowid'.
Server: Msg 207, Level 16, State 1, Procedure sp_LEISURE_Keyword_Search, Line 49
Invalid column name 'rowid'.
Server: Msg 207, Level 16, State 1, Procedure sp_LEISURE_Keyword_Search, Line 49
Invalid column name 'Keyword'.
Server: Msg 207, Level 16, State 1, Procedure sp_LEISURE_Keyword_Search, Line 49
Invalid column name 'Keyword'.

Any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-01 : 07:48:15

well you could use union to get rid of duplicates:

select top 50 *
from #SearchResults
union
select top 50 *
from #SearchResults

Go with the flow & have fun! Else fight the flow

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-10-01 : 07:57:51

Sorry, you've lost me, would it slow it down a lot by creating a new table where Count([ItemID]) > @Item_Count and the grabbing the records from that table instead?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-01 : 08:03:38

well you said you want to delete duplicates. the easiest way to do that is by using union.

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2004-10-01 : 08:04:35

[CODE] INSERT #SearchResults
SELECT ItemID
FROM vActiveItems
WHERE Title like @like_text -- This should insert ItemID only once for each pass through the loop
OR Abstract like @like_text
OR Story like @like_text[/CODE]

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-10-01 : 08:05:26

I see. I've also just tried another alternative which I think is working, instead of having three inserts (one for each column) I've now just got one for each word, I think its done the trick, don't know why I didn't try it earlier, thanks for your help. :)

I'll have to look into Unions I think

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources