Changes between Version 2 and Version 3 of UseCase05

Timestamp:

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

Author:

236021

Comment:

--

UseCase05

@@ -17 +17 @@
   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'
+);
 }}}
 
@@ -38 +43 @@
 INSERT INTO medical_record_symptoms (record_id, symptom_id, severity)
 VALUES (
-  (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')),
-  (SELECT symptom_id FROM symptoms WHERE name = 'Headache'),
+  (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'),
+  (SELECT symptom_id FROM symptoms WHERE name = 'HEADACHE'),
   'MODERATE'
 );
@@ -48 +57 @@
 {{{
 #!sql
-INSERT INTO diagnosis (name, description, patient_id, doctor_id)
+INSERT INTO diagnosis (diagnosis_id, name, description, patient_id, doctor_id)
 VALUES (
+  (SELECT COALESCE(MAX(diagnosis_id), 0) + 1 FROM diagnosis),
   'Essential hypertension',
   'High blood pressure condition',
-  (SELECT patient_id FROM users WHERE username = 'maja.veljanova'),
-  (SELECT doctor_id FROM users WHERE username = 'elena.kirova')
+  (SELECT p.patient_id
+   FROM patients p
+   JOIN users u ON u.user_id = p.user_id
+   WHERE u.username = '1402994123456'),
+  (SELECT d.doctor_id
+   FROM doctors d
+   JOIN users u ON u.user_id = d.user_id
+   WHERE u.username = 'elena.kirova@medora.com')
 )
 RETURNING diagnosis_id;
@@ -70 +86 @@
 FROM diagnosis d
 JOIN doctors doc ON d.doctor_id = doc.doctor_id
-JOIN users u ON u.doctor_id = doc.doctor_id
+JOIN users u ON u.user_id = doc.user_id
 WHERE d.diagnosis_id = (SELECT MAX(diagnosis_id) FROM diagnosis);
 }}}