database - Very simple query on mySQL (beginner question) -

I have a very simple question to get started in SQL. I have defined 3 tables in my database: "doctor", "patient", "visit" for simplicity purposes, I have one-to-many relationships in these tables: a doctor has many patients, but one The patient can see only one doctor and a patient can tour many ... I have "visits" in my table, I have a field "timestamp" to know when a previous tour will be.

I want to create a "view" on a MySQL, where a newest patient will come for each doctor who came for the trip and only recently think that every doctor sees 10 patients a day That is, I want only for every doctor that he is the last man who paid him a visit.

How can I do this?

Thank you for your help

1 Edit

=> Thank you for your reply! Finally, I have chosen a more realistic approach, where a "patient" can see many "doctors", so my table "visit" 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- 9 18:10 6 2 3 2010-07-19 19:10 7 1 1 2010-07-19 20:10 

I have 3 patients and 3 doctors. For example, the patient 1 went to see the doctor twice, 1 doctor 2 times and once doctor 3

I would like to make my request so that for everyone (doctor, patient) I have a previous arrival. This question should not be returned to IED visit (2,3,4,5,6) and not 1, because the last visit is that the patient given to the doctor was not given at 20:10 and 14:10. How can I go about doing this?

Once, I appreciate your comments and your help. It really helps me improve my designs as I have started using SQL.

We use the Visits table to find the latest visits for each patient, group by patient ID And by selecting the maximum (visit time). We then cooperate with patients through patients with their doctor.

Choose Doctor, Patient, Go In After Last Patient (Choose Patient, Maxim (Visitation Time) Visit Last Visit Visit Group from patient ID) Latest visits to previous Visas. PetentIDI = Patients PetentID

Edit: I read the question again, and this will not give you much what you want. Since this will present a list of duplicate Doctor IDs, it will show the latest visit for all patients.

To get the latest visits for each doctor, select

Doctor, latest visit, SELECT p.doctorID, MAX (v.lastVisit) latest visits Join INNER patients from Vision in V.patientID = p.domerID Group by p.doctorID Join the Base Inner (Select Doctorate, Patient, Include In Victim With Veer Inner Infair In Last Vision v.patientID = p. Victims on patientID) Visitors on patient visits. DoctorID = Base.Dock Traidiaidi Given that two patients may not get the same doctor at the same time, there is no risk of not returning more than one row for each doctor.