Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: problem for a new student.....

    I have a problem which i can not solve...
    I am trying to do a select statement from 3 tables where the results are posted for a specific item. here is the problem as written
    "
    display the PART_ID, PART_DESC, and QTY_USED from the COMPONENT and PART tables for all of the parts used for SYSTEM_ID = 'STU3419' "

    I have been trying to write a select statement to get this information and for some reason, i just cannot get it.

    Any help in this would be greatly appreciated.
    i have tried this and many variations without any luck

    SELECT DISTINCT PART.PART_ID, PART.PART_DESC, COMPONENT.QTY_USED
    FROM COMPONENT, PART, SYSTEM
    WHERE SYSTEM.SYSTEM_ID =
    (SELECT SYSTEM.SYSTEM_ID
    FROM SYSTEM
    WHERE SYSTEM_ID = 'STU3419');

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This subquery is rather pointless, don't you think?
    Code:
     (SELECT SYSTEM.SYSTEM_ID
    FROM SYSTEM
    WHERE SYSTEM_ID = 'STU3419');
    Can you see that the answer must be 'STU3419'?
    That simplifies your query to:
    Code:
    SELECT DISTINCT PART.PART_ID, PART.PART_DESC, COMPONENT.QTY_USED
    FROM COMPONENT, PART, SYSTEM
    WHERE SYSTEM.SYSTEM_ID = 'STU3419';
    Now all you are missing is some joins between the tables!

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Also since the question says "...from the COMPONENT and PART tables...", I'm guessing you don't need the SYSTEM table (which is an odd choice for a table name since there is already a user account called that). Do COMPONENT or PART have a column named SYSTEM_ID?

    Another tip would be not to use DISTINCT until you are sure it is necessary. Say you miss out a join condition, so SQL returns all possible combinations of COMPONENT and PART (for example), resulting in several hundred or thousand rows. If you put a DISTINCT at the top it may not be obvious what's happening, and you'll be left wondering why the query took a long time to run and gave the wrong results.
    Last edited by WilliamR; 11-20-04 at 12:54.

  4. #4
    Join Date
    Nov 2004
    Posts
    3

    here are the tables to help understand my delema...

    SQL> DESCRIBE PART
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    PART_ID NOT NULL VARCHAR2(18)
    PART_DESC VARCHAR2(30)
    PART_PRICE NUMBER(6,2)
    PART_ONHAND NUMBER(4)
    PART_TYPE CHAR(3)

    SQL>
    SQL> DESCRIBE SYSTEM
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    SYSTEM_ID NOT NULL CHAR(7)
    SYSTEM_DESC VARCHAR2(50)
    SYSTEM_PRICE NUMBER(6,2)

    SQL>
    SQL> DESCRIBE COMPONENT
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    SYSTEM_ID NOT NULL CHAR(7)
    PART_ID NOT NULL VARCHAR2(18)
    QTY_USED NOT NULL NUMBER(2)



    have to have system Id because that is where STu.....is?


    here is the entire DB

    DROP TABLE COMPONENT;
    DROP TABLE SYSTEM;
    DROP TABLE PART;


    CREATE TABLE PART
    (PART_ID VARCHAR2(18) NOT NULL,
    PART_DESC VARCHAR2(30),
    PART_PRICE NUMBER(6,2),
    PART_ONHAND NUMBER(4),
    PART_TYPE CHAR(3),
    CONSTRAINT PART_PK PRIMARY KEY (PART_ID));

    CREATE TABLE SYSTEM
    (SYSTEM_ID CHAR(7) NOT NULL,
    SYSTEM_DESC VARCHAR2(50),
    SYSTEM_PRICE NUMBER(6,2),
    CONSTRAINT SYSTEM_PK PRIMARY KEY (SYSTEM_ID));

    CREATE TABLE COMPONENT
    (SYSTEM_ID CHAR(7) NOT NULL,
    PART_ID VARCHAR2(18) NOT NULL,
    QTY_USED NUMBER(2) NOT NULL,
    CONSTRAINT COMPONENT_PK PRIMARY KEY (SYSTEM_ID, PART_ID),
    CONSTRAINT COMPONENT_FK1 FOREIGN KEY (SYSTEM_ID)
    REFERENCES SYSTEM (SYSTEM_ID),
    CONSTRAINT COMPONENT_FK2 FOREIGN KEY (PART_ID)
    REFERENCES PART (PART_ID));

    INSERT INTO SYSTEM VALUES
    ('STU3419', 'Celeron 700/20GB/64MB RAM', 500.00);

    INSERT INTO SYSTEM VALUES
    ('BUS0350', 'Pentium III 700/40GB/128MB', 750.00);

    INSERT INTO SYSTEM VALUES
    ('GMR1200', 'Pentium III 1GHz/40GB/128MB', 1100.00);

    INSERT INTO PART VALUES
    ('BX80526F700128', 'Intel Celeron 700Mhz/128K L2', 70.00, 5, 'CPU');

    INSERT INTO PART VALUES
    ('BX80526U700256E', 'Intel Pentium III 700Mhz', 140.00, 4, 'CPU');

    INSERT INTO PART VALUES
    ('BX80526H1000256', 'Intel Pentium III 1GHz', 260.00, 2, 'CPU');

    INSERT INTO PART VALUES
    ('D872120', '64MB Module', 30.00, 10, 'RAM');

    INSERT INTO PART VALUES
    ('DV658902', '128MB Module', 50.00, 20, 'RAM');

    INSERT INTO PART VALUES
    ('SG0234879B', '20GB Hard Drive', 95.00, 0, 'HDD');

    INSERT INTO PART VALUES
    ('SG1452890C', '40GB Hard Drive', 120.00, 8, 'HDD');

    INSERT INTO COMPONENT VALUES
    ('STU3419', 'BX80526F700128', 1);

    INSERT INTO COMPONENT VALUES
    ('STU3419', 'D872120', 1);

    INSERT INTO COMPONENT VALUES
    ('STU3419','SG0234879B', 1);

    INSERT INTO COMPONENT VALUES
    ('BUS0350', 'BX80526U700256E', 1);

    INSERT INTO COMPONENT VALUES
    ('BUS0350', 'SG1452890C', 1);

    INSERT INTO COMPONENT VALUES
    ('BUS0350', 'DV658902', 1);

    INSERT INTO COMPONENT VALUES
    ('GMR1200', 'BX80526H1000256', 1);

    INSERT INTO COMPONENT VALUES
    ('GMR1200', 'SG1452890C', 1);

    INSERT INTO COMPONENT VALUES
    ('GMR1200', 'DV658902', 1);

    COMMIT;

  5. #5
    Join Date
    Nov 2004
    Posts
    3

    the problem is I cannot figure out the join...

    this is what i had already, which is about what you suggested....


    PART_ID PART_DESC QTY_USED
    ------------------ ------------------------------ ----------
    BX80526F700128 Intel Celeron 700Mhz/128K L2 1
    BX80526H1000256 Intel Pentium III 1GHz 1
    BX80526U700256E Intel Pentium III 700Mhz 1
    D872120 64MB Module 1
    DV658902 128MB Module 1
    SG0234879B 20GB Hard Drive 1
    SG1452890C 40GB Hard Drive 1


    i need to get this to list ONLY those items used by STU....
    Thanks for the help to everyone.....

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select p.part_id,p.part_desc,c.qty_used
    from part p,
    COMPONENT c
    where p.part_id = C.part_id
    and c.system_id = 'STU3419' ;


    The only reason that I am helping you is because you did try. In the future, try not to forget the table joins.
    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
  •