Combine results of different WHERE clauses

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)

Combine results of different WHERE clauses

Author

Topic

nanite2000
Starting Member

3 Posts

Posted - 2008-09-27 : 13:51:32

Hi,

I am running a query that performs a match in 3 different stages:

1. Exact match on word and parameter
2. Match where parameter begins with word (fuzzy match 1)
3. Match where parameter occurs anywhere in the word (fuzzy match 2)

Normally I would do the following:

select name from names where name = @inputName
UNION
select name from names where name like @inputName + '%'
UNION
select name from names where name like '%' + @inputName + '%'

and this works fine producing the following results:

NAME
-------
Smith
Smiths
Smithy
John-Smith

HOWEVER I also wish to know the accuracy of the result from the search parameter. So the exact match would have a rank of '1', the fuzzy match 1 would have a rank of '2', and the fuzzy match 2 would have a rank of '3'. The idea is to keep the most accurate matches at the top of the results list.

I can change my query to the following:

select name, '1' as rank from names where name = @inputName
UNION
select name, '2' as rank from names where name like @inputName + '%'
UNION
select name, '3' as rank from names where name like '%' + @inputName + '%'

But this creates duplicate rows on the 'name' column:

NAME RANK
------------------
Smith 1
Smith 2
Smiths 2
Smithy 2
Smith 3
Smiths 3
Smithy 3
John-Smith 3

How can I perform this query such that fuzzy match 2 will only return results not present in the exact match, and fuzzy match 3 will only return results not present in fuzzy match 2, but still have a column that shows that accuracy of the search results?

In other words, I want my results to look like this:

NAME Rank
------------------
Smith 1
Smiths 2
Smithy 2
John-Smith 3

Can this be done in a single SQL statement? Or is there another, more efficient, way of doing this type of query?

Thanks for any help you can offer!

Rob.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 14:09:14

[code]SELECT t.Name,MIN(t.Rank) AS [Rank]
FROM
(
select name, '1' as rank from names where name = @inputName
UNION ALL
select name, '2' as rank from names where name like @inputName + '%'
UNION ALL
select name, '3' as rank from names where name like '%' + @inputName + '%'

)t
GROUP BY t.Name[/code]