Very simple query on mySQL (beginner question)

Question

Hi ! I have a very simple question for a beginner in SQL. I have defined 3 tables in my database : "DOCTOR", "PATIENTS", "VISITS". For simplicity purposes, I have only a one-to-many relationship btw these tables : One doctor has many patients, but a patient can see only one doctor and one patient can make many visits... in my table "VISITS" I have a field "timestamp" to know when was the last visit.

I want to make a "view" on MySQL where I will have for each doctor the latest patient that came for a visit, and only the most recent one. Let's say each doctor sees 10 patient a day, I just want for each doctor the last guy that paid a visit to him.

How can I do such a view ?

Thanks for your help

EDIT 1

=> THANKs FOR your answers !

I finally opted for a more realistic approach where a "patient" can see many "doctor" Therefore, my table "VISITS" will look like this :

idVisit  idDoctor  idPatient  timestamp
1        1         1          2010-07-19 14:10
2        1         2          2010-07-19 15:10
3        2         1          2010-07-19 15:10
4        3         1          2010-07-19 16:10
5        2         2          2010-07-19 18:10
6        2         3          2010-07-19 19:10
7        1         1          2010-07-19 20:10

I have then 3 patients and 3 doctors. For example, the patient 1 went two times to see doctor 1, one time doctor 2 and one time doctor 3.

I would like to build my request so that for each (doctor, patient) i have the last visit. This query should return the id Visits (2,3,4,5,6) and not 1 because the last visit that the patient paid to doctor 1 was at 20:10 and not 14:10. How could I do that ?

One more time, I really appreciate you comments and your help. It really helps me to improve my designs as I begin using SQL.

Answer 1

this is an SQL question, if you give your table schema we can give you the actual correct sql

I would do

select Doctor, max(visit) from doctor inner join patients on xxx inner join vistits on yyy

this gives you the a ist of doctors and last vist - join this to visits to get patient, job done

Answer 2

We use the Visits table to find the latest visit for each patient, by grouping by the patient ID and selecting the MAX(visitTime). We then associate the patient with his/her doctor, via the Patients table.

SELECT doctorID, patientID, lastVisit FROM
   Patients INNER JOIN (
      SELECT patientID, MAX(visitTime) AS lastVisit FROM Visits
      GROUP by patientID) latestVisits
   ON lastVisits.patentID = Patients.patentID

EDIT: I re-read the question, and this will not give you quite what you want. Since it will list duplicate doctor IDs, showing the latest visit for all patients.

To get the latest visit for each doctor,

   SELECT doctorID, latestVisit, (SELECT p.doctorID, MAX(v.lastVisit) latestVisit
     FROM Visits v INNER JOIN Patients p ON
       v.patientID = p.patientID
     GROUP BY p.doctorID) base INNER JOIN
      (SELECT doctorID, patientID, lastVisit FROM
        Visits v INNER JOIN Patients p ON
           v.patientID = p.patientID) patientVisits ON
        patientVisits.doctorID = base.doctorID AND patientVisits.lastVisit=base.lastVisit

Given that two patients can't see the same doctor at the same time, there is no risk of more than one row being returned for each doctor.

Answer 3

Here's a modified version of @mdma's query which does what I think you want:

SELECT doctorID, Patients.patientID, lastVisit FROM
   Patients INNER JOIN (
      SELECT patientID, MAX(time) AS lastVisit FROM Visits
      GROUP by doctorID) latestVisits
   ON latestVisits.patientID = Patients.patientID

Here's what I assumed your schema looks like:

Doctors:  doctorID
Patients: patientID, doctorID
Visits:   doctorID, patientID, time