0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
OfirH
  • 651
  • 1
  • 8
  • 19
  • 1
    possible duplicate of [How to check for duplicates in mysql table over multiple columns](http://stackoverflow.com/questions/6454805/how-to-check-for-duplicates-in-mysql-table-over-multiple-columns) – jmail Apr 04 '14 at 09: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 Apr 04 '14 at 09: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 Apr 04 '14 at 09: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 Apr 04 '14 at 09:44
  • Okay, but again: What do you want to compare: wether the telephone lists intersect or wether the phone lists equal? – Thorsten Kettner Apr 04 '14 at 10:08
  • I need to check if one of the phones of the specific client on the excell tabl is matching one of the phones on the `clients_to_call` table. If so I wouldn't insert this client to the `client_to_call` table. – OfirH Apr 04 '14 at 10:13

2 Answers2

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 ('', '-') );
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
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';
StanislavL
  • 56,971
  • 9
  • 68
  • 98