View Full Version : sql query help

12-01-2011, 09:35 PM
hey guys so i need a little help writing out this sql query:
I have 4 tables:
patients, prescriptions, UOM, and medications

in my patients table i want to get patients .med_rec, patients .fname, patients .lname

in my prescriptions table i want to get prescriptions.dose

in my UOM table i just want to get UOM.name

in my medications table i just want to get medications.med_name

okay here is where i am confused trying to get the right data to display

in my prescriptions table i have 3 foreign keys:
pat_id, uom_id, and med_id

in each of these fields they contain the patient.patient_id, UOM.unit_id, and medications.medication_id

i want to say something like SELECT patients .med_rec, patients .fname, patients .lname medications.med_name prescriptions.dose UOM.name
where patients.patient_id = 18;
*(later on i will make a variable that will store the patient_id that the user has chosen from a table and pass that where i have 18 right now)

Old Pedant
12-01-2011, 09:58 PM
Very very standard, very very simple JOIN:

SELECT p.med_rec, p.fname, p.lname, m.med_name, u.name, pr.dose
FROM prescriptions AS pr, patients AS p, medications AS m, UOM as u
WHERE pr.pat_id = p.patient_id
AND m.med_id = pr.med_id
AND u.uom_id = pr.uom_id
AND p.patient_id = $PATIENT_ID

The last condition could also be given as

AND pr.pat_id = $PATIENT_ID

Either one is fine.

12-01-2011, 10:11 PM
okay so i just copied and pasted the query you gave me in my sql terminal and i got this error:

unknown column 'm.med_id' in where clause

Old Pedant
12-01-2011, 10:16 PM
So use whatever the right column name is. You didn't bother to show us all the field names in all your tables, so I just used what you gave us.

FWIW, I *ALWAYS* use the same name for my primary keys as I do for my foreign keys.

So if I would have

CREATE TABLE prescriptions (
int med_id REFERENCES medications(med_id);

But if the field in medications is "medication_id" then that's what I would use as the foreign key name in prescriptions. Etc.

In general, I never name a field just "id". And other than primary/foreign key pairs, I try to never use the same field name in any two tables that might be related in any way.

12-01-2011, 10:16 PM
Never mind found the error got it working now thanks!!!!