List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:December 21 2005 7:59pm
Subject:Re: Matching phone numbers to addresses
View as plain text  
Russell,

 >... I need to get any matches on table 1 and then populate them to the
 >rest of the family at the address. So in the example above, Mary would
 >also get a phone number - I only have a first initial in table 2, so
 >that would hot Joe and James.

 From what you say, I am unclear why Mary gets a phone number and Joe 
does not, and I understand neither "I only have a first initial in 
table2" (the one row you show has FInitial='Joe') nor "that would hot 
Joe and James" (typo?).

Your tables aren't 3NF; they are likely candidates for many difficulties 
(of the sort you describe) with queries and updates. The problem is 
information redundancy. A suggestion:
  Add an auto-increment PK INT named nameID to the first table ("t1").
  Populate the column.
  Add auto-increment PK INT addressID to the second table ("t2").
  Populate the column.
  Make a persons table with columns nameID, firstname, initials, 
lastname, addressID .
  Populate the new persons table, eliminating dupes, from t1.
  Populate persons with rows (if any) from t2 for which there's no 
matching name in persons.
  Populate persons.addressID from t2 matching as desired on names
  Make an addresses table with auto-increment addressID plus columns for 
address info.
  Populate addresses with non-dupes from t1 and t2
Now you have one persons row for each person and one copy of each 
address, ie you have a normalised persons & addresses tables.

In this day of multiple phone numbers, a recommended enhancement: 
normalise the phone numbers too by breaking out all phone numbers to a 
new table phonenos (phoneID INT auto_increment, phone_no CHAR(14), 
phone_type ('home', 'work', 'cell', etc)), adding a phoneID column to 
addresses, then populating the phones table.

HTH.

PB

-----

Russell Horn wrote:

>I have a couple of tables like so:
>
>FName			FInitial
>SName			SName
>Address1		Address1
>Address2		Address2
>Address3		Address3
>Zip			Zip
>			Phone
>
>I need to match the phone numbers to the addresses in table 1.
>
>Trouble is, Table 1 has data like:
>
>Joe, Bloggs, 25 Frontier St,, BigTown, 12345
>Mary, Bloggs, 25 Frontier St,, BigTown, 12345
>James, Bloggs, 25 Frontier St,, BigTown, 12345
>
>Table 2 would only have 
>
>Joe, Bloggs, 25 Frontier St,, BigTown, 12345, (111) 555-1234
>
>So... I need to get any matches on table 1 and then populate them to the
>rest of the family at the address. So in the example above, Mary would
>also get a phone number - I only have a first initial in table 2, so
>that would hot Joe and James.
>
>Any thoughts on a strategy to do this? If I need to go outside MySQL to
>do some programming I'd be using php5.
>
>Thanks for any pointers.
>
>Russell.
>
>
>
>
>
>
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.3/209 - Release Date: 12/21/2005

Thread
Cleaning illegal characters from varchar fieldNathan Gross20 Dec
  • Re: Cleaning illegal characters from varchar fieldSGreen21 Dec
    • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
      • Re: Cleaning illegal characters from varchar fieldSGreen21 Dec
        • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
          • Re: Cleaning illegal characters from varchar fieldSGreen21 Dec
          • Re: Cleaning illegal characters from varchar fieldHassan Schroeder21 Dec
            • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
              • Re: Cleaning illegal characters from varchar fieldHassan Schroeder21 Dec
                • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
                  • Matching phone numbers to addressesRussell Horn21 Dec
                    • Re: Matching phone numbers to addressesPeter Brawley21 Dec
                  • Re: Cleaning illegal characters from varchar fieldHassan Schroeder21 Dec
                    • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
                  • Re: Cleaning illegal characters from varchar fieldMark Matthews21 Dec
                    • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec
      • Re: Cleaning illegal characters from varchar fieldJamesDR21 Dec
        • Re: Cleaning illegal characters from varchar fieldNathan Gross21 Dec