Write SQL for Microsoft Access scenario Given Tables Assignm

Write SQL for Microsoft Access scenario

Given Tables

Assignment Table - Attributes: Physician_ID, Patient_No, Role

Diagnosis Table- Attributes: Patient_No , Illness_Code

Illness Table- Attributes: Illness_Code, Illness_Desc , Department

Medication Table- Attributes: Medication_Code, Medication_Desc , On-Hand, On_Order, Unit_Cost

Patient Table - Attributes: Patient_No, Last_Name, First_Name, Gender, Age, Admit_Date, Room, Bed

Physician Table- Attributes: Physician_ID, Last_Name, First_Name, Tel_Ext, Specialty

Prescription Table- Attrubites: Pres_No, Patient_No, Medication_Code, Dosage

Question:

List the patients who do not have a prescription. The list should show the patient\'s name, gender, age, admission date, room, and illness description.

[Use combination of Join and nested sub query]

Solution

Select distinct P.Last_Name,P.First_Name,P.Gender,P.Age, P.Admit_Date,P.Room,D.Illness_Desc from Patient P join Diagnosis D on P.Patient_No = D.Patient_No LEFT Join Illness I on D.Illness_Code = I.Illness_Code where not exists(Select * from Prescription where Patient.Patient_No = Prescription.Patient_No);