I run a query using phone as criteria/condition (where xx.phone=yy.phone). I got 75 matches based on the phone numbers

However, if I deleted the phone numbers from set 2 data, used below query, the return result is only 45 matches. My question is what technique that I can use to optimize the result just based on criteria like first, last, and address.

rmiao: thanks. I think it's good idea to do that and identify the pattern of other columns. I have one last question on this. How can I use something like vowel algorithm or _similarity to compare these 2 sets of data? Thanks

This algorithm was developed specifically to do what you are asking. I've been using if for more than 10 years. http://sqlblindman.googlepages.com/fuzzysearchalgorithmSOUNDEX is not really appropriate for this. As a matter of fact, I'm not sure how many things SOUNDEX is useful for, except as a linguistic curiosity.

how can i put this result into a new table? I tried to use something like this but it gave me syntax error. I also tried to use insert statement, but nothing work. Please advises and thank you so much!

This is quite a complicated issue.I suggest use scoring to set up a matching threashold and to filter matching records.You have to create scoring rules:1. exact match on first name, last name, < other criterion>, <score>2. exact match on first name initial, surname, <other criterion>, <score>....all othere matching criteriasIt all depends on business requirements; how complicated your matching engine should be.Good luck.