Getting the data out of one-many relation ship from same table

Question

Hi,

I have a single table and it contains 4 columns:

Id|Hospital|   Doctor|patient
1     A        D1      P11
2     B        D6      P61
3     A        D2      P21
4     A        D1      P12
5     B        D7      P71
6     B        D6      P62
7     B        D6      P63

Doctors are unique to the Hospital. They don't work in other hospitals. Patients are unique to the doctor. They don't visit any other doctor. Each hospital has multiple Doctors.

If you observe there are multiple patients for each doctor.

Now the question is, How can I get "only one patient" related to each doctor? It can be any patient from the record.

I am looking forward to seeing something like this:

 Hospital Doctor Patient
  A       D1      P11
  A       D2      P21
  B       D6      P61
  B       D7      P71

How do I get the id also in the above table like:

id Hospital Doctor Patient
 1   A       D1      P11
 3   A       D2      P21
 2   B       D6      P61
 5   B       D7      P71

Thanks in advance

Answer 1

Like this:

SELECT Hospital, Doctor, MAX(Patient)
FROM table
GROUP BY Hospital, Doctor
ORDER BY Hospital, Doctor

Since you said you don't care which patient, I just used MAX, but you could use MIN or some other grouping function as well.

Answer 2

I can't help but ask if this shouldn't be three tables, not one. Certainly there's a 1:m relationship between Hospital and Doctor, and another 1:m between Doctor and Patient. Each entity must have other attributes. Why are all these in a single table? I'd normalize it.

I don't understand the requirement that would say P61 or P63 should be preferred for D6 in hospital B, but I'm sure you do.

Answer 3

Eric has answered the question, given the above table.

In case you have control over the database design:

If a doctor can only work in one hospital, then the Doctor table should have a HospitalId. And if a patient can really only have one doctor, then the Patient table should have a DoctorId.

Patient >> Doctor >> Hospital.

That would then eliminate this table altogether, which looks to support a many-to-many relationship between Doctors, Hospitals and Patients, which you say is not possible. You might want to remain flexible however.