Compare multiple rows in same table

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

Transact-SQL (2008)

Compare multiple rows in same table

Author

Topic

g.c.benjamin
Starting Member

10 Posts

Posted - 2014-04-27 : 20:53:39

I need to compare multiple rows in the same table to find duplicates. Forget how the data is setup, this is just some test data to try get the result I want for a much larger query.

create table Persons
(
  ID int,
  Name varchar(30),
  Value varchar(30),
  Description varchar(30)
);

insert into Persons values (1,'John','Surname','Smith');
insert into Persons values (1,'John','Age','50');
insert into Persons values (1,'John','Mobile','123456');
insert into Persons values (2,'John','Surname','Smith');
insert into Persons values (2,'John','Age','50');
insert into Persons values (2,'John','Mobile','987654');
insert into Persons values (3,'John','Surname','Smith');
insert into Persons values (3,'John','Age','50');
insert into Persons values (3,'John','Mobile','123456');
insert into Persons values (4,'Anne','Surname','Smith');
insert into Persons values (4,'Anne','Age','42');
insert into Persons values (4,'Anne','Mobile','123456');

What I need from the data above is the rows with an ID of 1 and 3. ID 2 is a different John judging from the phone number so I am not worried about it, however ID 1 and ID 3 is the same John so I need to know about these

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-27 : 22:01:07

what is the criteria ? just the Mobile alone or Mobile + Surname ?

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

g.c.benjamin
Starting Member

10 Posts

Posted - 2014-04-27 : 22:58:59

All 3 are the criteria. Any one of the three can be different, just so long as all 3 (Surname, Age, Mobile) aren't the same. If Surname, Age, Mobile are the same AND name ('john'), then return the records that are identical. So for each 'John' record, I need to test it against all the other 'John' records and make sure there is not another matching 'John' with the same Surname, Age, Mobile.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-27 : 23:58:08

[code]; with dup as
(
select p1.ID
from Persons p1
inner join Persons p2 on p1.ID <> p2.ID
and p1.Name = p2.Name
and p1.Value = p2.Value
and p1.Description = p2.Description
group by p1.ID, p2.ID
having count(*) = 3
)
select p.*
from dup d
inner join Persons p on d.ID = p.ID[/code]

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

g.c.benjamin
Starting Member

10 Posts

Posted - 2014-04-28 : 20:52:17

Thanks KH, You've got me a step closer and I thought if I had that little bit worked out I could tweak the rest myself, obviously I'm not as smart as I thought I was :)

The actual table holds a bunch of metadata, and there may not always be the same amount of values. In the sample data I provided there are always 3 rows for each person, but I may have other values in there totally unrelated that I also need to check for duplicates that only have 2 records for each. See updated schema.

create table Persons
(
ID int,
Name varchar(30),
Value varchar(30),
Description varchar(30)
);

insert into Persons values (1,'John','Surname','Smith');
insert into Persons values (1,'John','Age','50');
insert into Persons values (1,'John','Mobile','123456');
insert into Persons values (2,'John','Surname','Smith');
insert into Persons values (2,'John','Age','50');
insert into Persons values (2,'John','Mobile','987654');
insert into Persons values (3,'John','Surname','Smith');
insert into Persons values (3,'John','Age','50');
insert into Persons values (3,'John','Mobile','123456');
insert into Persons values (4,'Anne','Surname','Smith');
insert into Persons values (4,'Anne','Age','42');
insert into Persons values (4,'Anne','Mobile','123456');
insert into Persons values (5,'House','Street','Tree St');
insert into Persons values (5,'House','PostCode','12231');
insert into Persons values (6,'House','Street','Flower St');
insert into Persons values (6,'House','PostCode','56894');
insert into Persons values (7,'House','Street','Tree St');
insert into Persons values (7,'House','PostCode','895654');
insert into Persons values (8,'House','Street','Flower St');
insert into Persons values (8,'House','PostCode','56894');

So what needs to get returned is the same, 'John' with ID 1 and 3, but also 'House' with ID 6 and 8 ('House' with ID 5 and 7 are same street name but different post code so shouldn't be returned).

So basically I need to look at the Name column, find matching names and compare the matching names to make sure that there are no duplicate names with the same value and description.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-29 : 02:53:34

[code]; with dup as
(
select p1.ID
from Persons p1
inner join Persons p2 on p1.ID <> p2.ID
and p1.Name = p2.Name
and p1.Value = p2.Value
and p1.Description = p2.Description
group by p1.ID, p2.ID
having count(*) = (select count(*) from Persons x where x.ID = p1.ID)
)
select p.*
from dup d
inner join Persons p on d.ID = p.ID[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources