Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2011
    Posts
    7

    Unanswered: querying 2 tables and join.

    SQL> desc patient;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    P_ID NOT NULL NUMBER
    MRN NUMBER
    NAME VARCHAR2(30)
    ADMITTED_BY NUMBER
    REFFERED_BY NUMBER
    CCID NUMBER
    R_ID NUMBER
    BED_NUM NUMBER

    SQL> desc item;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ITEM_NUM NOT NULL NUMBER
    UNIT_COST NUMBER(38)
    DESCRIPTION VARCHAR2(20)

    SQL> desc items_used;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    I_ID NOT NULL NUMBER
    PATIENT_ID NOT NULL NUMBER
    DATE_USED NOT NULL DATE
    TIME_USED NOT NULL VARCHAR2(8)
    QUANTITY NUMBER
    TOTAL_COST NUMBER(38)



    query is asking me to give a patients name, and a description of the items they used where their mrn is equal to a certain value. cannot figure out how to only give these two pieces of information given these three tables.

    patient id is foreign key to p_id in patient. i_id is a foreign key of item_num in item. any help would be greately appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It would make it easier to assist you if you posted the CREATE TABLE statements for 3 tables.
    Also INSERT statements for sample data test SQL SELECT against.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    7
    Create table Patient
    (
    P_ID number primary key,
    MRN number,
    Name varchar2(30),
    Admitted_By references Physician(PH_ID),
    Reffered_By references physician(PH_ID),
    CCID number,
    R_ID number,
    Bed_num number,
    constraint fk_tblpatient foreign key(CCID,R_ID,Bed_num) references Bed( CCID,Room_num,bed_num)
    );

    Create table item
    (
    Item_Num number primary key,
    Unit_Cost decimal,
    description varchar2(20)
    );

    create table Items_Used
    (
    I_ID references item(Item_Num),
    Patient_ID references Patient(P_ID),
    Date_used Date,
    Time_used varchar2(8),
    quantity number,
    total_cost decimal,
    PRIMARY KEY(I_ID,Patient_ID,Date_used,Time_used)
    );

    insert into Patient
    values
    ('200','55671','Michel','101','106','1','113','01' );

    insert into Item
    values
    ('1000','5.00','Needles');

    insert into Items_Used
    values
    ('1000','203','14-JUL-11','8:30','3','30.00');

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT NAME,
           description
    FROM   patient pp,
           item ii,
           items_used iu
    WHERE  pp.p_id = iu.patient_id
           AND iu.i_id = ii.item_num;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2011
    Posts
    7
    Quote Originally Posted by anacedent View Post
    Code:
    SELECT NAME,
           description
    FROM   patient pp,
           item ii,
           items_used iu
    WHERE  pp.p_id = iu.patient_id
           AND iu.i_id = ii.item_num;
    thanx. i added an additional where clause to choose only a paticular patient based on mrn, since my teacher was working with joins, was there any way possible to do this with joins?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >was there any way possible to do this with joins?
    yes, but you need to contribute something to this exercise.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2011
    Posts
    7
    and what do i need to contribute?

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >and what do i need to contribute?
    valid syntax for the JOINs
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Oct 2011
    Posts
    7
    well i tried doing something in class like this:

    select name,description from patient p,item i inner join items_used on i.item_num=items_used.id inner join items_used on p.P_ID=Items_used.patient_ID where mrn='55671';

    but that gave me a syntax error. i didnt really study joins to well yet and this is an introductory course.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    fix the syntax error
    Code:
    SQL> set term on echo on
    SQL> SELECT NAME,
           description
    FROM   patient p,
           item i
           inner join items_used
             ON i.item_num = items_used.id
           inner join items_used
             ON p.p_id = items_used.patient_id
    WHERE  mrn = '55671';    2    3    4    5    6    7    8    9  
    	 ON i.item_num = items_used.id
                             *
    ERROR at line 6:
    ORA-00904: "ITEMS_USED"."ID": invalid identifier
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I thought that we didn't hand an answer to homework without seeing what they already tried and give hints, not the solution.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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