检查多列上的重复行

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?

Answer 1

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 ('', '-') );