SQL. Find doctors' names who have all patients with age less than 30-CodePudding

I have 3 tables Doctor, Patient, DoctorPatient. Doctor has fields ID,name,phone. Patient has fields ID,name,phone,age,sex. DoctorPatient contains the DoctorID and PatienId, that's the link between Doctor and Patient tables. How can i find doctors who have all patients with age less than 30?

There are relationships between tables

CodePudding user response：

If you group by the doctor, then the total patients have to be the same as the total patients under 30.

SELECT Doctor.name
, COUNT(IIF(Patient.age < 30, Patient.ID, null)) AS TotalPatientsUnder30
, COUNT(Patient.ID) AS TotalPatients
FROM Doctor 
JOIN DoctorPatient AS dopa ON dopa.DoctorID = Doctor.ID
JOIN Patient ON Patient.ID = dopa.PatientID
GROUP BY Doctor.ID, Doctor.name
HAVING COUNT(Patient.ID) = COUNT(IIF(Patient.age < 30, Patient.ID, null))
   AND COUNT(Patient.ID) > 0

CodePudding user response：

gives sql:

SELECT Doctors.DoctorName, Max(Patients.age) AS MaxOfage
FROM Patients INNER JOIN (Doctors INNER JOIN DoctorPatient ON Doctors.ID = DoctorPatient.DoctorID) ON Patients.ID = DoctorPatient.PatientID
GROUP BY Doctors.DoctorName
HAVING (((Max(Patients.age))<30));