Help with Search query (LIKE statement)

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 2005 Forums

Transact-SQL (2005)

Help with Search query (LIKE statement)

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-09-02 : 14:01:03

Hello,

This query, which search through a "friends" table for a social networking site, returns way too many results (about 50 rows when there is only 1 user that matches the criteria).

The reason why I have to do is a UNION is because the "Friends" table looks like this:

FriendID (int)
UserID (int)

A "friend ID" can be in either row, so if your userid is 61 then if FriendID = 61 and UserId = 51 then 51 is your friend, or if UserId = 51 and FriendID = 101 then 101 is your friend. Make sense?

Here is the query:



SELECT  
	A.userid
	, A.FriendId
	, A.SortOrder
	, A.Status
	, A.dateAdded
	, B.UserName
	, B.Quote 
FROM 
	Wisetopic_friend A
INNER JOIN 
	WiseTopic_User B  
ON 
	A.FriendId = B.UserId 
WHERE 
	B.userid=@userid AND
	B.displayname like '%' + @searchstring + '%' OR
	B.username like '%' + @searchstring + '%' OR
	B.fname like '%' + @searchstring + '%' OR
	B.lname like '%' + @searchstring + '%'

UNION

SELECT 
 A.FriendId AS UserId
, A.UserId AS FriendId
, A.SortOrder
, A.Status
, A.dateAdded
, B.UserName
, B.Quote
FROM 
	Wisetopic_friend A
INNER JOIN 
	WiseTopic_User B  
ON 
	A.UserId  = B.UserId
WHERE 
	B.userid=@userid AND
	B.displayname like '%' + @searchstring + '%' OR
	B.username like '%' + @searchstring + '%' OR
	B.fname like '%' + @searchstring + '%' OR
	B.lname like '%' + @searchstring + '%'
GO

I think its a very simple problem, maybe my LIKE statements are too greedy?

Thank you very much!

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 20:07:51

Are you getting a lot of duplication in the results set? Have you tried adding DISTINCT?

------------------------
Future guru in the making.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-02 : 22:20:16

perhaps you miss out the parenthesis ?


WHERE 
	B.userid=@userid AND (
	B.displayname like '%' + @searchstring + '%' OR
	B.username like '%' + @searchstring + '%' OR
	B.fname like '%' + @searchstring + '%' OR
	B.lname like '%' + @searchstring + '%' )

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 04:03:46

Also see http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=72097

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources