Checking for duplicate rows on multiple column

Question

I have a page which uploading excel file and insert it to the clients_to_call table.

On client_to_call there is among others the columns phone1,phone2 and phone3.

When the user uploads an Excel file, he's been asking to match between the Excel file column to the clients_to_call columns this way:

private name : (select tag with all of the Excel columns)
last name : (select tag with all of the Excel columns)
phone1 : (select tag with all of the Excel columns)
phone2 : (select tag with all of the Excel columns)
phone3 : (select tag with all of the Excel columns)
....

I'm trying to check if uploaded client is already in the clients_to_call table base on his phone number.

I have two things that interrupted me: every client has 3 different phone numbers. and their are not necessarily on the same column

The user can add client1 like that:

client_to call: phone1 // Excel: home_phone
client_to call: phone2 // Excel: mobile_phone
client_to call: phone3 // Excel: work_phone

And later add the same client like that:

client_to call: phone1 // Excel: work_phone
client_to call: phone2 // Excel: home_phone
client_to call: phone3 // Excel: mobile_phone

In addition, phone can be empty or contain only - and of course I don't want them to be considered as the same client.

Any help or suggestion?

Thank you, shabat shalom.

EDIT:

I could do it with a really inefficient and messy way. But every Excel file contains about 5000 clients, so the real question here is how do I make it in the most efficient way?

possible duplicate of How to check for duplicates in mysql table over multiple columns — jmail
– jmail, Commented Apr 4, 2014 at 9:17
If you would bother to read the content and not only the title you would know it isn't a duplicate of that question. — OfirH
– OfirH, Commented Apr 4, 2014 at 9:19
So when do you consider two client entries to be the same? When they have at least one telephone number in common? But that could also happen when they live together (a couple) or work in the same company and state the central office number. Or when all their numbers match? But the client may have given his/her mobile phone number one time and the other time not. So what rule shall apply? — Thorsten Kettner
– Thorsten Kettner, Commented Apr 4, 2014 at 9:37
@ThorstenKettner I will also add checks for his city and name but I dont have problem with that. only with the phones because of the reasons I mentioned at the question. — OfirH
– OfirH, Commented Apr 4, 2014 at 9:44
Okay, but again: What do you want to compare: wether the telephone lists intersect or wether the phone lists equal? — Thorsten Kettner
– Thorsten Kettner, Commented Apr 4, 2014 at 10:08

Thorsten Kettner · Accepted Answer · 2014-04-04 10:44:15Z

2

You will have to check every phone in the table against the list and make sure not to get a match on '' or '-'. That's more or less:

select * 
from clients
where 
( phone1 in (home_phone, mobile_phone, work_phone) and phone1 not in ('', '-') )
or
( phone2 in (home_phone, mobile_phone, work_phone) and phone2 not in ('', '-') )
or
( phone2 in (home_phone, mobile_phone, work_phone) and phone2 not in ('', '-') );

answered Apr 4, 2014 at 10:44

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

StanislavL · Accepted Answer · 2014-04-04 09:29:43Z

1

You can implement a function where pass all the 6 phones (3 from the fields and 3 from excel file) and compare them. The function (let's name it phoneCompare(...)) return true if the phones are actually the same.

Then the function could be used in

select *
from client_call
where phoneCompare(<all the fields and params>)=='different phones';

answered Apr 4, 2014 at 9:29

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

2 Comments

OfirH Over a year ago

I can use php functions on SQL query? I tried a few time before and it didn't worked.

StanislavL Over a year ago

It's not php. Define MySQL function

Collectives™ on Stack Overflow

Checking for duplicate rows on multiple column

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related