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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
