I have a situation and I need someone to help me with a query. I have 2 tables, one called User (this saves all the users that log in the system, both doctors and patients) and another Doctor-Patient with 2 fields, one is the id of the patient and the other is that of the corresponding doctor.
What I need is a query that shows me the name (found in the User table) of all the patients of a given doctor (is the one who is currently logged in the system)
Well, several data are missing but I will intuit that it is a sql database. So the query is generalized, (only translate it to the correct form as the case may be). (SQLServer, MySQL, SQLite, posgress, etc).
SELECT Usuario.Nombre, Doctor.Nombre, Usuario.Id, Doctor.id
FROM Usuarios
JOIN Doctores
ON Usuario.Id = Doctor.Id
WHERE Doctor.Id = $variable
You only have to pass the variable of the doctor that is logged in, this will bring you all the matches that comply with:
Doctor.Id = $variable
and that
Usuario.Id = Doctor.ID
capture the id of the doctor .. when looking for the list of their patients the example would be like this ..
SELECT * FROM doc_pacientes inner join personas on persona_id = doc_pacientes.fk_persona WHERE doc_paciente.id = $docLogueado
$ doc_logueado will be your variable that stores the id of the doctor and ready .. grouped by the id of the doctor .. and will bring you only 1 doctor and the rest in patients :) .. or just that it brings you the information of the patient