Nested Multi-Table Equi-Joins : Table Join Basics « Table Joins « Oracle PL / SQL






Nested Multi-Table Equi-Joins

   
SQL>
SQL>
SQL> CREATE TABLE empExam (
  2     empID  INT NOT NULL,
  3     ExamID     INT NOT NULL,
  4     Mark       INT,
  5     Taken   SMALLINT,
  6     Comments   VARCHAR(255),
  7     CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));

Table created.

SQL>
SQL>
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,1,55,1,'Satisfactory');

1 row created.

SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,2,73,1,'Good result');

1 row created.

SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,3,44,1,'Hard');

1 row created.

SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,5,39,0,'Simple');

1 row created.

SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken) VALUES (2,6,63,1);

1 row created.

SQL>
SQL>
SQL> CREATE TABLE Exam (
  2     ExamID      INT NOT NULL PRIMARY KEY,
  3     CourseID    INT NOT NULL,
  4     InstructorID INT NOT NULL,
  5     SustainedOn DATE,
  6     Comments    VARCHAR(255));

SQL>
SQL> CREATE TABLE emp (
  2     empID INT NOT NULL PRIMARY KEY,
  3     Name      VARCHAR(50) NOT NULL);

Table created.

SQL> INSERT INTO emp (empID,Name) VALUES (1,'Tom');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (2,'Jack');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (3,'Mary');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (4,'Bill');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (5,'Cat');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (6,'Victor');

1 row created.

SQL>
SQL> SELECT empExam.ExamID,
  2         empExam.Mark,
  3         Exam.SustainedOn,
  4         emp.Name
  5  FROM Exam
  6     INNER JOIN (emp
  7        INNER JOIN empExam ON emp.empID = empExam.empID)
  8     ON Exam.ExamID = empExam.ExamID
  9  ORDER BY empExam.ExamID;

    EXAMID       MARK SUSTAINED
---------- ---------- ---------
NAME
--------------------------------------------------
         1         55 12-MAR-03
Tom

         2         73 13-MAR-03
Tom

         3         44 11-MAR-03
Jack

         5         39 19-MAR-03
Jack


4 rows selected.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL> drop table empExam;

Table dropped.

SQL> drop table exam;

Table dropped.

   
    
    
  








Related examples in the same category

1.The query shows that the join is performed with the other WHERE conditions
2.Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
3.Used a GROUP BY in a query with no ordering or analytical function
4.Adding Ordering to the Query Containing the GROUP BY
5.Supplying Table Aliases
6.Add the USING clause
7.Join table using
8.Join with a subquery
9.Get Categories and Products (with Alternate Join Syntax)
10.Get Categories and Products (with Joins)
11.Joining table to use between ... and clause
12.How Many Featured Products By Department with JOINs
13.How Many Products By Department with JOINs
14.Non-Equi Joins