Changes between Version 2 and Version 3 of UseCase03

Timestamp:

09/22/26 14:18:14 (23 hours ago)

Author:

236021

Comment:

--

UseCase03

@@ -16 +16 @@
   mr.patient_id
 FROM medical_records mr
-WHERE mr.patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova');
+WHERE mr.patient_id = (
+    SELECT p.patient_id
+    FROM patients p
+    JOIN users u ON u.user_id = p.user_id
+    WHERE u.username = '1402994123456'
+);
 }}}
 
@@ -31 +36 @@
 FROM diagnosis diag
 JOIN doctors d ON diag.doctor_id = d.doctor_id
-JOIN users u ON u.doctor_id = d.doctor_id
-WHERE diag.patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova');
+JOIN users u ON u.user_id = d.user_id
+WHERE diag.patient_id = (
+    SELECT p.patient_id
+    FROM patients p
+    JOIN users usr ON usr.user_id = p.user_id
+    WHERE usr.username = '1402994123456'
+);
 }}}
 
@@ -45 +55 @@
 FROM medical_record_symptoms mrs
 JOIN symptoms s ON mrs.symptom_id = s.symptom_id
-WHERE mrs.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
+WHERE mrs.record_id = (
+    SELECT mr.record_id
+    FROM medical_records mr
+    JOIN patients p ON mr.patient_id = p.patient_id
+    JOIN users u ON u.user_id = p.user_id
+    WHERE u.username = '1402994123456'
+);
 }}}
 
@@ -58 +74 @@
 FROM medical_record_allergies mra
 JOIN allergies a ON mra.allergy_id = a.allergy_id
-WHERE mra.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
+WHERE mra.record_id = (
+    SELECT mr.record_id
+    FROM medical_records mr
+    JOIN patients p ON mr.patient_id = p.patient_id
+    JOIN users u ON u.user_id = p.user_id
+    WHERE u.username = '1402994123456'
+);
 }}}
 
@@ -72 +94 @@
 JOIN lab_results lr ON mrlr.result_id = lr.result_id
 JOIN lab_tests lt ON lr.test_id = lt.test_id
-WHERE mrlr.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
+WHERE mrlr.record_id = (
+    SELECT mr.record_id
+    FROM medical_records mr
+    JOIN patients p ON mr.patient_id = p.patient_id
+    JOIN users u ON u.user_id = p.user_id
+    WHERE u.username = '1402994123456'
+);
 }}}
 
@@ -84 +112 @@
 FROM medical_record_procedures mrp
 JOIN procedures pr ON mrp.procedure_id = pr.procedure_id
-WHERE mrp.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
+WHERE mrp.record_id = (
+    SELECT mr.record_id
+    FROM medical_records mr
+    JOIN patients p ON mr.patient_id = p.patient_id
+    JOIN users u ON u.user_id = p.user_id
+    WHERE u.username = '1402994123456'
+);
 }}}
 
@@ -98 +132 @@
 FROM prescription_medical_records pmr
 JOIN prescriptions p ON pmr.prescription_id = p.prescription_id
-WHERE pmr.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
+WHERE pmr.record_id = (
+    SELECT mr.record_id
+    FROM medical_records mr
+    JOIN patients pt ON mr.patient_id = pt.patient_id
+    JOIN users u ON u.user_id = pt.user_id
+    WHERE u.username = '1402994123456'
+);
 }}}
 
@@ -113 +153 @@
 FROM medical_report rep
 JOIN doctors d ON rep.doctor_id = d.doctor_id
-JOIN users u ON u.doctor_id = d.doctor_id
-WHERE rep.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
+JOIN users u ON u.user_id = d.user_id
+WHERE rep.record_id = (
+    SELECT mr.record_id
+    FROM medical_records mr
+    JOIN patients p ON mr.patient_id = p.patient_id
+    JOIN users usr ON usr.user_id = p.user_id
+    WHERE usr.username = '1402994123456'
+);
 }}}