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
