Search results withouth duplicates

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)

Search results withouth duplicates

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-06-03 : 04:17:47

I'm using this SQL code to search the database from ASP pages:

--declare @word varchar(30)
--set @word='%foo%'

select top 200 K.K_TopicID, K.K_Topic, M.M_Reply
from dbo.[TOPICS] as K left outer join dbo.[REPLIES] as M
on K.K_TopicID = M.M_TopicID
where (M.M_Reply like @word) or (K.K_Topic like @word) or (K.K_TopicMessage like @word)

The problem is, if a topic has replies then the results return duplicates of that topic with number of the replies. For example, the topic "foo" with ID 234 has 3 replies and the results show 3 copies of it.

I couldn't understand what causing this but I can get rid of it with eliminating duplicate ID's in results. I couldn't figured out how to do it, I can't use Distinct or Group By because the fields M_Reply and K_TopicMessage are text datatypes. How should I do this?

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-06-03 : 07:40:59

Try SELECT DISTINCT...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-03 : 07:54:17

quote:
Try SELECT DISTINCT...

Try reading the original a little more closely:

quote:
I can't use Distinct or Group By because the fields M_Reply and K_TopicMessage are text datatypes

If you modify the query a bit, you can turn the TOP 200 part into a subquery and remove the text columns, then join the outer part to it to get the text columns back:

SELECT T.K_TopicID, T.K_Topic, R.M_Replyselect
FROM (SELECT DISTINCT TOP 200 K.K_TopicID
FROM dbo.[TOPICS] as K LEFT JOIN dbo.[REPLIES] as M ON K.K_TopicID = M.M_TopicID
WHERE M.M_Reply like @word OR K.K_Topic like @word OR K.K_TopicMessage like @word) A
INNER JOIN Topics T ON A.K_Topic_ID=T.K_Topic_ID
LEFT JOIN Replies R ON A.K_Topic_ID=R.M_Topic_ID

I believe performance will be pretty crappy, but try it and see.

Edited by - robvolk on 06/03/2002 07:55:28

kensai
Posting Yak Master

172 Posts

Posted - 2002-06-04 : 06:35:46

Thanks for the help robvolk. But your code returns exactly same results as mine, along with the duplicates :)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-04 : 09:09:29

as a last resort you can always....


select top 200 K.K_TopicID, K.K_Topic, M.M_Reply 
into #results
from dbo.[TOPICS] as K left outer join dbo.[REPLIES] as M 
on K.K_TopicID = M.M_TopicID 
where (M.M_Reply like @word) or (K.K_Topic like @word) or (K.K_TopicMessage like @word) 

select distinct K_Topicid from #results

<O>

kensai
Posting Yak Master

172 Posts

Posted - 2002-06-04 : 15:46:36

I hope I won't need that last resort. I'm always anxious about temporary tables. I guess I'm afraid of it's performance. I do hope we could find another solution for this..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-04 : 16:32:08

<well-intentioned rant>
HAVE YOU TRIED USING THE TEMP TABLE SOLUTION YET???????? HOW DO YOU KNOW HOW IT WILL PERFORM UNLESS YOU'VE TRIED IT???????

It kinda bothers me that everyone keeps ASSUMING that a temp table will perform badly. I cannot count the number of times I've used temp tables, and except for one very slow procedure (not matter what I did) temp tables have been stellar performers.

Sorry, but you've got no reason to avoid temp tables unless you've actually tested that code and found the performance completely shitty. BTW, as long as you're using LIKE comparisons, it really doesn't matter what you do, the performance is gonna suck, temp tables or not.
</well-intentioned rant>

kensai
Posting Yak Master

172 Posts

Posted - 2002-06-05 : 16:51:41

I tried temp tables once and I remember it doing very bad, I'm not talking without reason.

Okay, okay I'm gonna try it, no need to shout. I swear to god I'm gonna use it and nothing else for the rest of my life.

*shudder*

:)

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-05 : 17:00:25

If you are concerned about performance with Temp tables, and you have SQL Server 2000, look up table variables.
They do the same thing as a temp table, except they are in-memory and thus much faster than Temp tables which are written to the temdb, on disk.

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources