Extension of question many to many relationships in same table.

Question

I got a single table and it contain 4 fields

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 is having 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 the each doctor. It can be any patient from the record.

I am looking forward to see some thing like this

 Hospital Doctor Patient
  A       D1      P11

  A       D2      P21

  B       D6      P61

  B       D7      P71

I got the answer like select Hospital,doctor, max(patient) from table GROUP BY Hospital,Doctor ORDER BY Hospital,Doctor;

How to get the id also which is unique from the above table like.

id Hospital Doctor Patient
 1   A       D1      P11

 3   A       D2      P21

 2   B       D6      P61

 5   B       D7      P71

I am very sorry to repost this question.

Answer 1

Try something like:

select Id,Hospital,Doctor,Patient
  from table
  where Id in (select max(t.Id) from table t group by t.Hospital,t.Doctor)
  order by Hospital,Doctor;

Answer 2

SELECT  m.*
FROM    (
        SELECT  (
                SELECT  id
                FROM    mytable mi
                WHERE   mi.hospital = md.hospital
                        AND mi.doctor = md.doctor
                LIMIT 1
                ) AS first_patient
        FROM    (
                SELECT  DISTINCT hospital, doctor
                FROM    mytable
                ) md
        ) mo, mytable m
WHERE   m.id = mo.first_patient

Answer 3

You might look at breaking things into three tables: Hospitals (with Primary Key id, and the Hospital field), Doctors (with someother PK, a Foreign Key of Hospitals, and the Doctor field) and Patients (with someother PK, a Foreign Key of Doctors, and the Patient field). Then your statement would look something like:

SELECT H.Id, H.Hospital, D.Doctor, Max(P.Patient)
FROM Hospitals H
INNER JOIN Doctors D ON H.Hospital = D.Hospital
INNER JOIN Patients P ON D.Doctor = P.Doctor
ORDER BY Hospital, Doctor