comparing multiple rows mysql

[benjaminbeazy]

hi, im trying to consolidate a table with doctor information
currently i have the multiple entries of the same doctor with the same "upin" id
but different rows have different addresses, specialties, and so on

basically, this is what i want to do

compare these 3 fields across the separate rows with same upin:
address, specialty, board_certified
and if i get any differences between them to store it in a newly set up table with multiple fields for address, and specialty

this doesnt make much sense, here, example:

i want to take this

UPIN      Address      Specialty    Board Certified
123      123 main    cardio          no
123      123 main    osteo          yes
123      678 first      cardio          no

and turn it into this, in a new table

UPIN      Address 1      Address 2      Address 3        Specialty1    Spec2      Spec3    Board Cert
123        123 main        678 first                              cardio        osteo                    yes

i hope that is more clear...

[Barand]

[quote]this doesnt make much sense, [/quote]
I have to agree.

The first thing to eliminate when designing tables is repeating data fields, so I could understand if you wanted to convert from the new format to the old but not what you are proposing. Also your proposed new table loses information - which specialiality/address does the "yes" refer to?

What happens if you have someone with 4 addresses?

[shazam]

Ben,

What is your ultimate goal with the table?  Is it to make querying easier for the application that uses it?  Your new table doesn't seem to be scalable (like Barand says, what if a Doctor has 4 addresses).

If I understand more about how your application is supposed to use this data, I may be able to provide some suggestions around design of the database.

- B

[Jessica]

I agree, the first layout is a much more logical layout.