Write a query in SQL to Obtain the names of all patients who had at least two appointment where the nurse who prepped the appointment
- برمجة سي كيو ال sql
- 2021-09-06
- mhanasmh00489829403
الأجوبة
SELECT pt.name AS "Patient",
p.name AS "Primary Physician",
n.name AS "Nurse"
FROM appointment a
JOIN patient pt ON a.patient=pt.ssn
JOIN nurse n ON a.prepnurse=n.employeeid
JOIN physician p ON pt.pcp=p.employeeid
WHERE a.patient IN
(SELECT patient
FROM appointment a
GROUP BY a.patient
HAVING count(*)>=2)
AND n.registered='true'
ORDER BY pt.name;
Sample Output:
Patient | Primary Physician | Nurse ---------------+-------------------+----------------- Dennis Doe | Christopher Turk | Laverne Roberts Grace Ritchie | Elliot Reid | Carla Espinosa Grace Ritchie | Elliot Reid | Carla Espinosa John Smith | John Dorian | Carla Espinosa John Smith | John Dorian | Laverne Roberts (5 rows)
أسئلة مشابهة
القوائم الدراسية التي ينتمي لها السؤال