SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Compare multiple rows in same table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

g.c.benjamin
Starting Member

10 Posts

Posted - 04/27/2014 :  20:53:39  Show Profile  Reply with Quote
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)

Singapore
17638 Posts

Posted - 04/27/2014 :  22:01:07  Show Profile  Reply with Quote
what is the criteria ? just the Mobile alone or Mobile + Surname ?


KH
Time is always against us

Go to Top of Page

g.c.benjamin
Starting Member

10 Posts

Posted - 04/27/2014 :  22:58:59  Show Profile  Reply with Quote
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.

Edited by - g.c.benjamin on 04/27/2014 23:01:51
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 04/27/2014 :  23:58:08  Show Profile  Reply with Quote
; 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



KH
Time is always against us

Go to Top of Page

g.c.benjamin
Starting Member

10 Posts

Posted - 04/28/2014 :  20:52:17  Show Profile  Reply with Quote
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.

Edited by - g.c.benjamin on 04/29/2014 01:00:49
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 04/29/2014 :  02:53:34  Show Profile  Reply with Quote
; 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



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000