Finding uniqueness of person with different email mobile combination in SQL.

Question

1.24/5 (3 votes)

See more:

Hi Friends,

Need to find the uniqueness of person with email id and phone number in SQL.

For Example, we have data like below.

Phone, Email, Id
111, abc@gmail.com, 1
112, abc@gmail.com, 2
112, pqr@hotmail.com, 3
113, abc@gmail.com, 4
211, someoneelse@live.com, 5

If you check above data then this data is of two people with different email id and phone numbers but the person is one unique how to find this uniqueness in data using SQL query.

I need the output as below with new column person having different Email id (Gmail, Hotmail) and phones (111,112,113) which are related to each other in some place phone number is related in other email id is related.

Person, Phone, Email, Id
1,111, abc@gmail.com, 1
1,112, abc@gmail.com, 2
1,112, pqr@hotmail.com, 3
1,113, abc@gmail.com, 4
2,211, someoneelse@live.com, 5

Please, requesting early response as spending lots of time.

Thanks and Regards,
Santosh Sawant

What I have tried:

Try to create different hash table but can't manage uniqueness.

Posted 19-Apr-17 23:12pm

Sasmi_Office

Updated 22-Apr-17 5:24am

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kosmas kafataridis · Answer 1 · 2017-04-21T21:58:00

I can not explain it please try and if not i will try

CREATE TABLE #test (test1 nvarchar(50),test2 nvarchar(50),test3 int);
CREATE TABLE #uniquephone(ID bigint IDENTITY(1,1) NOT NULL, phone nvarchar(50));
CREATE TABLE #uniquemail(ID bigint IDENTITY(1,1) NOT NULL, mail nvarchar(50));
CREATE TABLE #uniqueall(ID bigint IDENTITY(1,1) NOT NULL, id2 bigint);
CREATE TABLE #uniqueall2uniquephone(ID bigint NOT NULL, id2 bigint);
insert into #test (test1, test2, test3) values ('111', 'abc@gmail.com', 1);
insert into #test (test1, test2, test3) values ('112', 'abc@gmail.com', 2);
insert into #test (test1, test2, test3) values ('112', 'pqr@hotmail.com', 3);
insert into #test (test1, test2, test3) values ('113', 'abc@gmail.com', 4);
insert into #test (test1, test2, test3) values ('211', 'someoneelse@live.com', 5);
insert into #uniquephone (phone)  select distinct test1 from #test;
insert into #uniquemail (mail)  select distinct test2 from #test;
insert into #uniqueall (id2)  select distinct min(id2) id2 from (select a.id, c.id id2, test1, test2, test3 from #uniquephone a inner join #test on a.phone=#test.test1 inner join #uniquemail c on #test.test2=c.mail) a group by id
insert into #uniqueall2uniquephone (ID, id2)  select distinct min(id2) id2, id from (select a.id, c.id id2, test1, test2, test3 from #uniquephone a inner join #test on a.phone=#test.test1 inner join #uniquemail c on #test.test2=c.mail) a group by id
select a.ID, #test.* from #uniqueall a inner join #uniqueall2uniquephone b on a.id2=b.ID inner join #uniquephone c on b.id2=c.ID inner join #test on c.phone=#test.test1 
drop table #test  
drop table #uniquephone  
drop table #uniquemail  
drop table #uniqueall  
drop table #uniqueall2uniquephone

#test is your table
outut:

ID	test1	test2	test3
1	111	abc@gmail.com	1
1	112	abc@gmail.com	2
1	112	pqr@hotmail.com	3
1	113	abc@gmail.com	4
2	211	someoneelse@live.com	5

CHill60 · Answer 2 · 2017-04-22T05:24:00

Here's an alternative to Solution 1 which I believe is easier to follow. I created the test data as this:

SQL

create table #test 
(id int identity(1,1),phone nvarchar(123),Email nvarchar(123),Person int)

insert into #test (phone, Email) values
('111', 'abc@gmail.com'), 
('112', 'abc@gmail.com'), 
('112', 'pqr@hotmail.com'), 
('113', 'abc@gmail.com'), 
('211', 'someoneelse@live.com')

First use a SQL Window function to give us proposed "Person" numbers based only on the email address:

SQL

UPDATE T SET Person = D.RN FROM #test T
INNER JOIN 
  (SELECT id, DENSE_RANK() OVER (ORDER BY Email) AS RN FROM #test) D on D.id = T.ID

This is the contents of #test after that:

id	Phone	Email			Person
1	111	abc@gmail.com		1
2	112	abc@gmail.com		1
3	112	pqr@hotmail.com		2
4	113	abc@gmail.com		1
5	211	someoneelse@live.com	3

This next statement corrects the situation for the Phone number ... i.e. it will replace Person = 2 with Person = 1 because they are the same "person". I've use MIN to get the earlier Person number but you could just as easily use MAX :

SQL

UPDATE T SET Person = newPerson
from (
     SELECT PHONE, min(person) as newPerson FROM #test 
     GROUP BY phone having count(*) > 1) AS q
inner join #test t on q.phone=t.phone

The contents of #test after that are:

id	Phone	Email			Person
1	111	abc@gmail.com		1
2	112	abc@gmail.com		1
3	112	pqr@hotmail.com		1
4	113	abc@gmail.com		1
5	211	someoneelse@live.com	3

You could just stop there as each person has a unique number (1 and 3) but if you really want the numbers to be consecutive then run this last piece. It renumbers each distinct Person using RANK but you could just as easily use ROW_NUMBER()

SQL

UPDATE T SET Person = Q2.NP 
FROM #test T
INNER JOIN (select Person, RANK() OVER (ORDER BY Person) AS NP FROM (select distinct Person from #test) Q1) Q2 ON Q2.Person=T.Person

The final results are

id	Phone	Email			Person
1	111	abc@gmail.com		1
2	112	abc@gmail.com		1
3	112	pqr@hotmail.com		1
4	113	abc@gmail.com		1
5	211	someoneelse@live.com	2

Here is the complete code done as one-liners to make it look shorter ;-)

SQL

create table #test (id int identity(1,1),phone nvarchar(123),Email nvarchar(123),Person int)
insert into #test (phone, Email) values
('111', 'abc@gmail.com'), 
('112', 'abc@gmail.com'), 
('112', 'pqr@hotmail.com'), 
('113', 'abc@gmail.com'), 
('211', 'someoneelse@live.com')
UPDATE T SET Person = D.RN FROM #test T INNER JOIN ( SELECT id, DENSE_RANK() OVER (ORDER BY Email) AS RN FROM #test) D on D.id = T.ID
UPDATE T SET Person = newPerson from (SELECT PHONE, min(person) as newPerson FROM #test GROUP BY phone having count(*) > 1) AS q inner join #test t on q.phone=t.phone
UPDATE T SET Person = Q2.NP FROM #test T INNER JOIN (select Person, ROW_NUMBER() OVER (ORDER BY Person) AS NP FROM (select distinct Person from #test) Q1) Q2 ON Q2.Person=T.Person
select * from #TEST
DROP TABLE #test

Finding uniqueness of person with different email mobile combination in SQL.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Finding uniqueness of person with different email mobile combination in SQL.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us