sql query help

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)

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.

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

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.

Never mind found the error got it working now thanks!!!!