1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Trying to Join Multiple Tables and Columns

Discussion in 'SQL PL/SQL' started by AngryPenguin, Apr 17, 2013.

  1. AngryPenguin

    AngryPenguin Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I am trying to display the lab description of the lab my employees belong to via a SELECT statement in SQL Developer. I have a table at the end that displays the LabID that each employee belongs to, but I cannot for the life of me figure out a way to add in the lab description (LabDescr) that goes along with the LabID.

    Here is my script:

    Code (SQL):

    CREATE TABLE tblEmployee
    (
        EmployeeID              INTEGER         NOT NULL,
        EmpFName                VARCHAR2 (25)   NOT NULL,
        EmpLName                VARCHAR2 (25)   NOT NULL,
        EmpSSN                  VARCHAR2 (12)   NOT NULL,
        JobTitle                VARCHAR2 (25),  
        CONSTRAINT tblEmployee_pk
            PRIMARY KEY (EmployeeID)   
    );

    CREATE TABLE tblLab
    (
        LabID                   INTEGER         NOT NULL,
        LabDescr                VARCHAR2(50),
        CONSTRAINT tblLab_pk
            PRIMARY KEY (LabID)
    );

    CREATE TABLE tblShift
    (
        ShiftID                 INTEGER         NOT NULL,
        DayofWeek               CHARACTER(3),
        StartTime               VARCHAR2 (5),
        EndTime                 VARCHAR2 (5),
        CONSTRAINT tblShift_pk
            PRIMARY KEY (ShiftID)
    );

    CREATE TABLE tblWorksOn
    (
        WorkID                  INTEGER     NOT NULL,
        EmployeeID              INTEGER     NOT NULL,
        LabID                   INTEGER     NOT NULL,
        CONSTRAINT tblWorksOn_pk
            PRIMARY KEY (WorkID),
        CONSTRAINT tblWorksOnEmpID_fk
            FOREIGN KEY (EmployeeID) REFERENCES tblEmployee(EmployeeID),
        CONSTRAINT tblWorksOnLab_fk
            FOREIGN KEY (LabID) REFERENCES tblLab(LabID)
    );

    CREATE TABLE tblEmployeeSchedule
    (
        AssignmentID            INTEGER     NOT NULL,
        EmployeeID              INTEGER     NOT NULL,
        LabID                   INTEGER     NOT NULL,
        ShiftID                 INTEGER     NOT NULL,
        WorkID                  INTEGER     NOT NULL,
        CONSTRAINT tblEmployeeSchedule_pk
            PRIMARY KEY (AssignmentID),
        CONSTRAINT tblEmployeeScheduleEmp_fk
            FOREIGN KEY (EmployeeID) REFERENCES tblEmployee(EmployeeID),
        CONSTRAINT tblEmployeeScheduleLab_fk
            FOREIGN KEY (LabID) REFERENCES tblLab(LabID),
        CONSTRAINT tblEmployeeScheduleShift_fk
            FOREIGN KEY (ShiftID) REFERENCES tblShift(ShiftID),
        CONSTRAINTS tblWorksOn_fk
            FOREIGN KEY (WorkID) REFERENCES tblWorksOn(WorkID)
    );

    -- Sequence
    CREATE SEQUENCE EmployeeID_seq
      START WITH 125
        INCREMENT BY 5;
    CREATE SEQUENCE LabID_seq
      START WITH 225
        INCREMENT BY 5;
    CREATE SEQUENCE WorksOnID_seq
        START WITH 335
            INCREMENT BY 5;
     
    -- Insert employees into db

    INSERT INTO tblEmployee
    (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
    VALUES (EmployeeID_seq.NEXTVAL,'Some', 'Guy', '123-45-6789', 'Chief Executive Officer');
    INSERT INTO tblEmployee
    (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
    VALUES (EmployeeID_seq.NEXTVAL,'Jimi', 'Hendrix', '124-78-9923', 'Musician');
    INSERT INTO tblEmployee
    (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
    VALUES (EmployeeID_seq.NEXTVAL, 'Linus', 'Torvalds', '332-05-6512', 'Software Engineer');
    INSERT INTO tblEmployee
    (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
    VALUES (EmployeeID_seq.NEXTVAL, 'Bill', 'Gates', '909-76-3467', 'Software Engineer');
    INSERT INTO tblEmployee
    (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
    VALUES (EmployeeID_seq.NEXTVAL, 'Clarke', 'Kent', '250-77-9843', 'Marketing');
    INSERT INTO tblEmployee
    (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
    VALUES (EmployeeID_seq.NEXTVAL, 'Tony','Stark', '221-32-3234', 'Electrical Engineer');
    INSERT INTO tblEmployee
    (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
    VALUES (EmployeeID_seq.NEXTVAL, 'Doc', 'Brown', '676-56-8892','Scientist');
    INSERT INTO tblEmployee
    (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
    VALUES (EmployeeID_seq.NEXTVAL, 'John','Grishsam', '120-45-2156', 'Accountant');

    -- Insert employee Labs into db

    INSERT INTO tblLab
    (LabID, LabDescr)
    VALUES (LabID_seq.NEXTVAL, 'Engineering');
    INSERT INTO tblLab
    (LabID, LabDescr)
    VALUES (LabID_seq.NEXTVAL, 'Particle Physics');
    INSERT INTO tblLab
    (LabID, LabDescr)
    VALUES (LabID_seq.NEXTVAL, 'Experimental Genetics');
    INSERT INTO tblLab
    (LabID, LabDescr)
    VALUES (LabID_seq.NEXTVAL, 'Extraterrestrial Relations ');
    INSERT INTO tblLab
    (LabID, LabDescr)
    VALUES (LabID_seq.NEXTVAL, 'Cryptology');

    -- Insert shift types into db

    INSERT INTO tblShift
    (ShiftID, DayofWeek, StartTime, EndTime)
    VALUES (1,'Mon', '07:00', '3:30');
    INSERT INTO tblShift
    (ShiftID, DayofWeek, StartTime, EndTime)
    VALUES (2,'Tue', '07:30', '3:30');
    INSERT INTO tblShift
    (ShiftID, DayofWeek, StartTime, EndTime)
    VALUES (3,'Wed', '07:30', '3:30');
    INSERT INTO tblShift
    (ShiftID, DayofWeek, StartTime, EndTime)
    VALUES (4,'Thu', '07:30', '3:30');
    INSERT INTO tblShift
    (ShiftID, DayofWeek, StartTime, EndTime)
    VALUES (5,'Fri', '07:30', '3:30');

    -- Insert into tblWorksOn

    INSERT INTO tblWorksOn
    (WorkID, EmployeeID, LabID)
    VALUES (WorksOnID_seq.NEXTVAL,125, 225);
    INSERT INTO tblWorksOn
    (WorkID, EmployeeID, LabID)
    VALUES (WorksOnID_seq.NEXTVAL,130, 225);
    INSERT INTO tblWorksOn
    (WorkID, EmployeeID, LabID)
    VALUES (WorksOnID_seq.NEXTVAL,135, 225);
    INSERT INTO tblWorksOn
    (WorkID, EmployeeID, LabID)
    VALUES (WorksOnID_seq.NEXTVAL,140, 225);
    INSERT INTO tblWorksOn
    (WorkID, EmployeeID, LabID)
    VALUES (WorksOnID_seq.NEXTVAL,145, 225);
    INSERT INTO tblWorksOn
    (WorkID, EmployeeID, LabID)
    VALUES (WorksOnID_seq.NEXTVAL,150, 225);
    INSERT INTO tblWorksOn
    (WorkID, EmployeeID, LabID)
    VALUES (WorksOnID_seq.NEXTVAL,155, 225);
    INSERT INTO tblWorksOn
    (WorkID, EmployeeID, LabID)
    VALUES (WorksOnID_seq.NEXTVAL,160, 225);

    COMMIT;
     
  2. AngryPenguin

    AngryPenguin Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Well, I have just noticed that the LabID fields in tblWorksOn are all the same. So when I ran:
    Code (SQL):
    SELECT
      e.EmpFName,
      e.EmpLName,
      l.LabDescr AS Labratory
    FROM
      tblEmployee e,
      tblWorksOn w,
      tblLab l
    WHERE
      e.EmployeeID = w.EmployeeID AND
      l.LabID = w.LabID ;
    It returned all the same department description. Either way, does anyone have better JOIN like statements?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That depends upon which release of Oracle you're using. I moldified your data somewhat and offer another query to perform the same operation, usng 11.2.0.3:

    Code (SQL):
    SQL>
    SQL> CREATE TABLE tblEmployee
      2  (
      3        EmployeeID         INTEGER NOT NULL,
      4        EmpFName         VARCHAR2 (25) NOT NULL,
      5        EmpLName         VARCHAR2 (25) NOT NULL,
      6        EmpSSN         VARCHAR2 (12) NOT NULL,
      7        JobTitle         VARCHAR2 (25),
      8        CONSTRAINT tblEmployee_pk
      9         PRIMARY KEY (EmployeeID)
     10  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE tblLab
      2  (
      3        LabID        INTEGER NOT NULL,
      4        LabDescr         VARCHAR2(50),
      5        CONSTRAINT tblLab_pk
      6         PRIMARY KEY (LabID)
      7  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE tblShift
      2  (
      3        ShiftID         INTEGER NOT NULL,
      4        DayofWeek         CHARACTER(3),
      5        StartTime         VARCHAR2 (5),
      6        EndTime         VARCHAR2 (5),
      7        CONSTRAINT tblShift_pk
      8         PRIMARY KEY (ShiftID)
      9  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE tblWorksOn
      2  (
      3        WorkID         INTEGER NOT NULL,
      4        EmployeeID         INTEGER NOT NULL,
      5        LabID        INTEGER NOT NULL,
      6    CONSTRAINT tblWorksOn_pk
      7         PRIMARY KEY (WorkID),
      8        CONSTRAINT tblWorksOnEmpID_fk
      9         FOREIGN KEY (EmployeeID) REFERENCES tblEmployee(EmployeeID),
     10        CONSTRAINT tblWorksOnLab_fk
     11         FOREIGN KEY (LabID) REFERENCES tblLab(LabID)
     12  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE tblEmployeeSchedule
      2  (
      3        AssignmentID       INTEGER NOT NULL,
      4        EmployeeID         INTEGER NOT NULL,
      5        LabID         INTEGER NOT NULL,
      6        ShiftID         INTEGER NOT NULL,
      7        WorkID         INTEGER NOT NULL,
      8        CONSTRAINT tblEmployeeSchedule_pk
      9         PRIMARY KEY (AssignmentID),
     10        CONSTRAINT tblEmployeeScheduleEmp_fk
     11         FOREIGN KEY (EmployeeID) REFERENCES tblEmployee(EmployeeID),
     12        CONSTRAINT tblEmployeeScheduleLab_fk
     13         FOREIGN KEY (LabID) REFERENCES tblLab(LabID),
     14        CONSTRAINT tblEmployeeScheduleShift_fk
     15         FOREIGN KEY (ShiftID) REFERENCES tblShift(ShiftID),
     16        CONSTRAINTS tblWorksOn_fk
     17         FOREIGN KEY (WorkID) REFERENCES tblWorksOn(WorkID)
     18  );
     
    TABLE created.
     
    SQL>
    SQL> -- Sequence
    SQL> CREATE SEQUENCE EmployeeID_seq
      2    START WITH 125
      3        INCREMENT BY 5;
     
    SEQUENCE created.
     
    SQL> CREATE SEQUENCE LabID_seq
      2    START WITH 225
      3        INCREMENT BY 5;
     
    SEQUENCE created.
     
    SQL> CREATE SEQUENCE WorksOnID_seq
      2        START WITH 335
      3         INCREMENT BY 5;
     
    SEQUENCE created.
     
    SQL>
    SQL> -- Insert employees into db
    SQL>
    SQL> INSERT INTO tblEmployee
      2  (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
      3  VALUES (EmployeeID_seq.NEXTVAL,'Some', 'Guy', '123-45-6789', 'Chief Executive Officer');
     
    1 ROW created.
     
    SQL> INSERT INTO tblEmployee
      2  (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
      3  VALUES (EmployeeID_seq.NEXTVAL,'Jimi', 'Hendrix', '124-78-9923', 'Musician');
     
    1 ROW created.
     
    SQL> INSERT INTO tblEmployee
      2  (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
      3  VALUES (EmployeeID_seq.NEXTVAL, 'Linus', 'Torvalds', '332-05-6512', 'Software Engineer');
     
    1 ROW created.
     
    SQL> INSERT INTO tblEmployee
      2  (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
      3  VALUES (EmployeeID_seq.NEXTVAL, 'Bill', 'Gates', '909-76-3467', 'Software Engineer');
     
    1 ROW created.
     
    SQL> INSERT INTO tblEmployee
      2  (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
      3  VALUES (EmployeeID_seq.NEXTVAL, 'Clarke', 'Kent', '250-77-9843', 'Marketing');
     
    1 ROW created.
     
    SQL> INSERT INTO tblEmployee
      2  (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
      3  VALUES (EmployeeID_seq.NEXTVAL, 'Tony','Stark', '221-32-3234', 'Electrical Engineer');
     
    1 ROW created.
     
    SQL> INSERT INTO tblEmployee
      2  (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
      3  VALUES (EmployeeID_seq.NEXTVAL, 'Doc', 'Brown', '676-56-8892','Scientist');
     
    1 ROW created.
     
    SQL> INSERT INTO tblEmployee
      2  (EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
      3  VALUES (EmployeeID_seq.NEXTVAL, 'John','Grishsam', '120-45-2156', 'Accountant');
     
    1 ROW created.
     
    SQL>
    SQL> -- Insert employee Labs into db
    SQL>
    SQL> INSERT INTO tblLab
      2  (LabID, LabDescr)
      3  VALUES (LabID_seq.NEXTVAL, 'Engineering');
     
    1 ROW created.
     
    SQL> INSERT INTO tblLab
      2  (LabID, LabDescr)
      3  VALUES (LabID_seq.NEXTVAL, 'Particle Physics');
     
    1 ROW created.
     
    SQL> INSERT INTO tblLab
      2  (LabID, LabDescr)
      3  VALUES (LabID_seq.NEXTVAL, 'Experimental Genetics');
     
    1 ROW created.
     
    SQL> INSERT INTO tblLab
      2  (LabID, LabDescr)
      3  VALUES (LabID_seq.NEXTVAL, 'Extraterrestrial Relations ');
     
    1 ROW created.
     
    SQL> INSERT INTO tblLab
      2  (LabID, LabDescr)
      3  VALUES (LabID_seq.NEXTVAL, 'Cryptology');
     
    1 ROW created.
     
    SQL>
    SQL> -- Insert shift types into db
    SQL>
    SQL> INSERT INTO tblShift
      2  (ShiftID, DayofWeek, StartTime, EndTime)
      3  VALUES (1,'Mon', '07:00', '3:30');
     
    1 ROW created.
     
    SQL> INSERT INTO tblShift
      2  (ShiftID, DayofWeek, StartTime, EndTime)
      3  VALUES (2,'Tue', '07:30', '3:30');
     
    1 ROW created.
     
    SQL> INSERT INTO tblShift
      2  (ShiftID, DayofWeek, StartTime, EndTime)
      3  VALUES (3,'Wed', '07:30', '3:30');
     
    1 ROW created.
     
    SQL> INSERT INTO tblShift
      2  (ShiftID, DayofWeek, StartTime, EndTime)
      3  VALUES (4,'Thu', '07:30', '3:30');
     
    1 ROW created.
     
    SQL> INSERT INTO tblShift
      2  (ShiftID, DayofWeek, StartTime, EndTime)
      3  VALUES (5,'Fri', '07:30', '3:30');
     
    1 ROW created.
     
    SQL>
    SQL> -- Insert into tblWorksOn
    SQL>
    SQL> INSERT INTO tblWorksOn
      2  (WorkID, EmployeeID, LabID)
      3  VALUES (WorksOnID_seq.NEXTVAL,125, 225);
     
    1 ROW created.
     
    SQL> INSERT INTO tblWorksOn
      2  (WorkID, EmployeeID, LabID)
      3  VALUES (WorksOnID_seq.NEXTVAL,130, 230);
     
    1 ROW created.
     
    SQL> INSERT INTO tblWorksOn
      2  (WorkID, EmployeeID, LabID)
      3  VALUES (WorksOnID_seq.NEXTVAL,135, 235);
     
    1 ROW created.
     
    SQL> INSERT INTO tblWorksOn
      2  (WorkID, EmployeeID, LabID)
      3  VALUES (WorksOnID_seq.NEXTVAL,140, 240);
     
    1 ROW created.
     
    SQL> INSERT INTO tblWorksOn
      2  (WorkID, EmployeeID, LabID)
      3  VALUES (WorksOnID_seq.NEXTVAL,145, 245);
     
    1 ROW created.
     
    SQL> INSERT INTO tblWorksOn
      2  (WorkID, EmployeeID, LabID)
      3  VALUES (WorksOnID_seq.NEXTVAL,150, 240);
     
    1 ROW created.
     
    SQL> INSERT INTO tblWorksOn
      2  (WorkID, EmployeeID, LabID)
      3  VALUES (WorksOnID_seq.NEXTVAL,155, 225);
     
    1 ROW created.
     
    SQL> INSERT INTO tblWorksOn
      2  (WorkID, EmployeeID, LabID)
      3  VALUES (WorksOnID_seq.NEXTVAL,160, 230);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT
      2    e.EmpFName,
      3    e.EmpLName,
      4    l.LabDescr AS Labratory
      5  FROM
      6    tblEmployee e JOIN tblWorksOn w ON (w.EmployeeID = e.EmployeeID) JOIN tblLab l ON (l.Labid = w.LabID);
     
    EMPFNAME                  EMPLNAME                  LABRATORY                                                                      
    ------------------------- ------------------------- --------------------------------------------------                              
    SOME                      Guy                       Engineering                                                                    
    Jimi                      Hendrix                   Particle Physics                                                                
    Linus                     Torvalds                  Experimental Genetics                                                          
    Bill                      Gates                     Extraterrestrial Relations                                                      
    Clarke                    Kent                      Cryptology                                                                      
    Tony                      Stark                     Extraterrestrial Relations                                                      
    Doc                       Brown                     Engineering                                                                    
    John                      Grishsam                  Particle Physics                                                                
     
    8 ROWS selected.
     
    SQL>
     
     
  4. AngryPenguin

    AngryPenguin Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Interesting syntax. Originally, I tried a JOIN statement. My was syntax was very close to what you had but off a little. I have started to notice different syntax with different versions. Apparently, using joins is more efficient though.