I have a problem that I do not know how to solve. I have 3 tables: table1 appointments: Date_ID, date, Patient_ID. Patient table2: name, Patient_ID, Department_ID, department2 table: Apartment_ID, department_name.
I need to get the number of appointments for each department. For example, there is patient "X" which is associated with a department --- > Apartment ID Then he asks for an appointment which is associated with a patient --- > Patient_ID
You must relate the three tables either in the where or using an inner join and use a function that tells you what is grouped by patient and department
**
select paciente.nombre, departamento.nombre_departamento, count(citas.ID_Cita) as total_citas from citas
inner join paciente on paciente.ID_Paciente = citas.ID_Paciente
inner join departamento on departamento.ID_Departamento = paciente.ID_Departamento
group by paciente.nombre, departamento.nombre_departamento
**
You get the amount of each appointment in relation to the patient, you apply an inner join between the patient table and department
example:
SELECT count(select count(*) from citas where citas.ID_Paciente=paciente.ID_Paciente) as num_citas
,paciente.nombre
,departamento.nombre_departamento
FROM paciente
inner join departamento on departamento.ID_Departamento=paciente.ID_Departamento