Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: sql query help

  1. #1
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts

    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)

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,577
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Very very standard, very very simple JOIN:
    Code:
    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
    Code:
    AND pr.pat_id = $PATIENT_ID
    Either one is fine.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    VickP07 (12-01-2011)

  • #3
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    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

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,577
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    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
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Never mind found the error got it working now thanks!!!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •