I am trying to work with SQL in MS Access and am trying to do the following:
1. List the patients who do not have a prescription. The list should show the patient's name, gender, age, admission date, and illness description.
[Use combination of Join and nested sub query]
Here is my SQL..which is almost correct
SELECT Last_name, First_name, Gender, Age, Admit_date FROM Patient WHERE NOT EXISTS (SELECT * FROM Prescription WHERE Patient.Patient_no = Prescription.Patient_no);
The problem is that if i try to add Illness des, i end up with error and do not know how to reference it in my table..WHAT AM I DOING WRONG??
Here is my second problem:
2. Produce a list of patients who were prescribed 'Penicillin'. The list should contain the patient name, medication name, dosage, illness description, and the doctor's name.
SELECT DISTINCT Last_name, First_name, Medication_desc, Dosage, Illness_desc, Physician_ID FROM Patient AS P, Diagnosis AS D, Illness AS I, Prescription AS E, Medication AS M, Physician AS Y, Assignment AS A WHERE (M.Medication_code=E.Medication_code) And (E.Patient_no=P.Patient_no) And (P.Patient_no=A.Patient_no) And (A.Physician_ID=Y.Physician_ID) And (M.Medication_desc="Penicillin");
This query will not even run. What's he problem??