Finding possible matches - SQL Server Forums

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

Other Forums

MS Access

Finding possible matches

Author

Topic

MaMaMiYa
Starting Member

1 Post

Posted - 2008-09-04 : 06:17:12

Hi, lets say I have the following 7 records in my database

ID Model
-- ------
1 400
2 400FL
3 400 FL
4 ABC
5 SGH500
6 SGH-500
7 SGH 500

How is it possible if i could select out the rows which are of possible match to some other rows?

E.g. IDs 1, 2 & 3 are possible matches of one another and IDs 5, 6 & 7 are possible matches of one another. ID 4 will not be displayed in the result.

Thanks a lot in advanced

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 07:59:05

You have to decide and explain what a "possible match" means to you.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-04 : 08:42:10

If it is always based on the first three characters,

select t1.* from table as t1 inner join
(
	select substring(model,1,3) as model from table
	group by substring(model,1,3) having count(*)>1
) as t2 on t1.model like t2.model+'%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources