Match 3 out of 4 fields and return them...

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)

Match 3 out of 4 fields and return them...

Author

Topic

smartlizard
Starting Member

24 Posts

Posted - 2001-11-29 : 08:32:03

I have a Stored Procedure that accepts input variables from an ASP page, then returns the recordset based on the parameters I pass it. Let's say I pass it a First Name, Last Name, SSN and a City.

I want to return the records that may match any three of the four. For instance, If I thought the city was Tampa, when it realy was Orlando it returns the record set because I did have the first name and last name and SSN correct. This is similar to a search engine where it returns listings that are less than 100% accurate.

Anyway to do this in T-SQL? What would be the code for this?

I am using SQL Server 2000.

Thanks!

Ron

Ron Sell
http://smartLIZARD.com

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2001-11-29 : 08:44:36

You could try something like this, I don't know if it will be slow.

select * from (
select case when ssn = @ssn then 1 else 0 end +
case when lastname = @lastname then 1 else 0 end +
case when firstname = @firstname then 1 else 0 end +
case when city = @city then 1 else 0 end as matches,persons.*
from persons
where ssn = @ssn
or city = @city
or firstname = @firstname
or lastname = @lastname ) as s
where s.matches >= 3
order by s.matches desc

izaltsman
A custom title

1139 Posts

Posted - 2001-11-29 : 08:45:43

Since you need 3 out of 4 attributes to match, just assign 1 "point" to a record for every attribute that matches... Any record that gets 3 or more points qualifies:

select * from mytable
where (CASE SSN WHEN @SSN THEN 1 ELSE 0 END
+ CASE FNAME WHEN @FNAME THEN 1 ELSE 0 END
+ CASE LNAME WHEN @LNAME THEN 1 ELSE 0 END
+ CASE CITY WHEN @CITY THEN 1 ELSE 0 END) >=3

And I've been sniped again! I am getting slow in my old age.

Edited by - izaltsman on 11/29/2001 08:48:09

smartlizard
Starting Member

24 Posts

Posted - 2001-11-30 : 00:47:08

This works great! Thanks so much.

Ron Sell

Ron Sell
http://smartLIZARD.com

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2001-11-30 : 09:12:42

I wrote some code that does something similar a while back. You may be able to modify it for your needs.

Searching on SQLTeam

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources