Selecting search matches OR a default

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)

Selecting search matches OR a default

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-13 : 09:46:58

Roger writes "This is a bit messy to describe! Rather simplified, I have two tables, one with addresses (A) and one with names (N), each name joined to an address by an ID field. I want to search for a string which may be in either the address or the name, and return a minimal but complete set of rows. By this I mean all the names that match, plus a single row for each address which matches without a name matching. Such as

Results for "Bob"
ABC Company (Bob Johnson)
ABC Company (Bob Zurich)
Bobsleigh Group (Anne Smith)
SQL Team (Robert Jones)

My best effort is this ... the NID term is so that I can format the results depending whether the match was in a name or address.


SELECT 
   A.Address1 as Address, 
   (SELECT TOP 1 N.Name FROM N 
   WHERE N.Card_id = A.Card_ID) as Name1,
   -1 as NID 
FROM A
WHERE A.Address1 + A.Address2 LIKE '%bob%' 
AND NOT EXISTS(SELECT N.Name FROM N
   WHERE A.Card_id = N.Card_ID AND N.Name LIKE '%bob%') 

UNION

SELECT 
   A.Address1 as Address, 
   N.Name as Name1,
   N.ID as NID
FROM A JOIN N on (N.Card_id = A.Card_ID) 
WHERE N.Name LIKE '%bob%'

ORDER BY Address, Name

This gets really messy when expanded so that (in real life) the target might be in several fields or even have one part in the name and one part in the address (find "sqlteam tibet"). Can you suggest anything elegant?

Best wishes, Roger."

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2001-12-13 : 17:41:53

Roger, Try it this way..

 
--<TSQL>--
Select Name1 = Case When A.Address1 like '%bob%' Then '(' + A.Address1 + ') ' + N.Name
	When N.Name like '%bob%' Then A.Address1 + ' (' + N.Name + ')' 
	End
From
	A
Join
	N
On A.CardID = N.CardID
Where A.Address1 + A.Address2 LIKE '%bob%' OR N.Name like '%Com%'
--<TSQL>--

I think this should work, plus it'll make it easy to add field to search by.. I think an inner join will suffice.. Hopefully that helps..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources