3-Way join : Table Join Column « Table Joins « Oracle PL / SQL






3-Way join

   
SQL>
SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );

Table created.

SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,'14-Feb-2002', 23.00, '14-Feb-2002', '12 noon', 'CA',1, null, 'Gift for wife');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,'14-Feb-2003', 510.98, '14-feb-2003', '5 pm', 'NY',7, 'Rose Ted', 'Happy Valentines Day to Mother');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(3, 2,'14-Feb-2004', 315.99, '14-feb-2004', '3 pm', 'VS',2, 'Ani Forest', 'Happy Valentines Day to Father');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(4, 2,'14-Feb-1999', 191.95, '14-feb-1999', '2 pm', 'NJ',2, 'O. John', 'Happy Valentines Day');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(5, 6,'4-mar-2002', 101.95, '5-mar-2002', '2:30 pm', 'MO'   , 2, 'Cora', 'Happy Birthday from John');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(6, 9,'7-apr-2003', 221.95, '7-apr-2003', '3 pm', 'MA', 2, 'Sake Keith', 'Happy Birthday from Joe' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(7, 9,'20-jun-2004', 315.95, '21-jun-2004', '12 noon', 'BC', 2, 'Jessica Li', 'Happy Birthday from Jessica');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (8, 12, '31-dec-1999', 135.95, '1-jan-2000', '12 noon', 'DI',      3, 'Larry', 'Happy New Year from Lawrence');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (9, 12, '26-dec-2003', 715.95, '2-jan-2004', '12 noon', 'SK',7, 'Did', 'Happy Birthday from Nancy' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(10, 4, sysdate-1, 119.95, sysdate+2, '6:30 pm', 'VG',2, 'P. Jing', 'Happy Valentines Day to Jason');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(11, 2, sysdate, 310.00, sysdate+2, '3:30 pm', 'DC',2, 'C. Late', 'Happy Birthday Day to Jack');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(12, 7, sysdate-3, 121.95, sysdate-2, '1:30 pm', 'AC',2, 'W. Last', 'Happy Birthday Day to You');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(13, 7, sysdate, 211.95, sysdate-4, '4:30 pm', 'CA',2, 'J. Bond', 'Thanks for hard working');

1 row created.

SQL>
SQL>
SQL>
SQL> create table department(
  2          dept_no                 integer      primary key
  3         ,dept_name               varchar(20)      not null
  4         ,mgr_no                  integer
  5  );

Table created.

SQL>
SQL> insert into department(dept_no, dept_name, mgr_no)values(1, 'Design', 1);

1 row created.

SQL> insert into department(dept_no, dept_name, mgr_no)values(2, 'Sales', 1);

1 row created.

SQL> insert into department(dept_no, dept_name, mgr_no)values(3, 'Development', 1);

1 row created.

SQL>
SQL> create table employee(
  2           emp_no                 integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,zip_4                  varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,salary                 number(3)
 14          ,birthdate              date
 15          ,hiredate               date
 16          ,title                  varchar2(20)
 17          ,dept_no                integer
 18          ,mgr                    integer
 19          ,region                 number
 20          ,division               number
 21          ,total_sales            number
 22  );

Table created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (1,'Anderson','Nancy','N','33 Ave','London','NY','11111','1111','212','234-1111',3.75,'21-mar-1927','1-feb-1947','Sales Manager',2,null,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (2,'Last','First','F','12 Ave','Paris','CA','22222','2222','111','867-2222',7.75,'14-feb-1976','15-mar-1985','Sales Clerk',2,1,100,10,10000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (3,'Wash','Georgia','G','1 Street14','Barton','NJ','33333','3333','214','340-3333',11.50,'2-jul-1977','21-apr-2004','Designer',1,2,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (4,'Bush','Dave','D','56 Street','Island','RI','44444','4444','215','777-4444',21.65,'15-may-1945','2-aug-1975','Designer',1,2,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (5,'Will','Robin','W','56 Street','Island','MA','55555','5555','216','777-5555',24.65,'10-dec-1980','2-aug-2007','Designer',1,5,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (6,'Pete','Mona','M','13 Ave','York','MO','66666','6666','217','111-6666',9,'14-feb-1966','15-mar-1985','Sales Clerk',2,5,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (7,'Roke','John','J','67 Ave','New York','BC','77777','7777','218','122-7777',10.00,'14-jun-1955','15-mar-1975','Accountant',3,2,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (8,'Horry','Tedi','T','1236 Lane','Newton','NY','88888','8888','219','222-8888',13.00,'10-jun-1955','15-aug-1985','Sales Representative',3,2,100,10,50000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2                values (9,'Bar','Candi','C','400 East Street','Yorken','NY','99999','9999','220','321-9999',12.00,'10-oct-1933','15-jan-1969','Sales Representative',3,5,100,10,35000);

1 row created.

SQL>
SQL> select * from ord;
  ORDER_NO    CUST_NO ORDER_DATE           TOTAL_ORDER_PRICE
---------- ---------- -------------------- -----------------
DELIVER_DATE         DELIVER PA     EMP_NO
-------------------- ------- -- ----------
DELIVER_NAME
-----------------------------------
GIFT_MESSAGE
------------------------------------------------------------------------
         1          1 14-FEB-2002 00:00:00                23
14-FEB-2002 00:00:00 12 noon CA          1

Gift for wife

         2          1 14-FEB-2003 00:00:00            510.98
14-FEB-2003 00:00:00 5 pm    NY          7
Rose Ted
Happy Valentines Day to Mother

         3          2 14-FEB-2004 00:00:00            315.99
14-FEB-2004 00:00:00 3 pm    VS          2
Ani Forest
Happy Valentines Day to Father

         4          2 14-FEB-1999 00:00:00            191.95
14-FEB-1999 00:00:00 2 pm    NJ          2
O. John
Happy Valentines Day

         5          6 04-MAR-2002 00:00:00            101.95
05-MAR-2002 00:00:00 2:30 pm MO          2
Cora
Happy Birthday from John

         6          9 07-APR-2003 00:00:00            221.95
07-APR-2003 00:00:00 3 pm    MA          2
Sake Keith
Happy Birthday from Joe

         7          9 20-JUN-2004 00:00:00            315.95
21-JUN-2004 00:00:00 12 noon BC          2
Jessica Li
Happy Birthday from Jessica

         8         12 31-DEC-1999 00:00:00            135.95
01-JAN-2000 00:00:00 12 noon DI          3
Larry
Happy New Year from Lawrence

         9         12 26-DEC-2003 00:00:00            715.95
02-JAN-2004 00:00:00 12 noon SK          7
Did
Happy Birthday from Nancy

        10          4 15-JUN-2008 17:39:15            119.95
18-JUN-2008 17:39:15 6:30 pm VG          2
P. Jing
Happy Valentines Day to Jason

        11          2 16-JUN-2008 17:39:16               310
18-JUN-2008 17:39:16 3:30 pm DC          2
C. Late
Happy Birthday Day to Jack

        12          7 13-JUN-2008 17:39:17            121.95
14-JUN-2008 17:39:17 1:30 pm AC          2
W. Last
Happy Birthday Day to You

        13          7 16-JUN-2008 17:39:17            211.95
12-JUN-2008 17:39:17 4:30 pm CA          2
J. Bond
Thanks for hard working


13 rows selected.

SQL> select * from employee;
    EMP_NO LASTNAME             FIRSTNAME       M
---------- -------------------- --------------- -
STREET                         CITY                 ST ZIP   ZIP_ ARE
------------------------------ -------------------- -- ----- ---- ---
PHONE        SALARY BIRTHDATE            HIREDATE
-------- ---------- -------------------- --------------------
TITLE                   DEPT_NO        MGR     REGION   DIVISION
-------------------- ---------- ---------- ---------- ----------
TOTAL_SALES
-----------
         1 Anderson             Nancy           N
33 Ave                         London               NY 11111 1111 212
234-1111          4 21-MAR-1927 00:00:00 01-FEB-1947 00:00:00
Sales Manager                 2                   100         10
      40000

         2 Last                 First           F
12 Ave                         Paris                CA 22222 2222 111
867-2222          8 14-FEB-1976 00:00:00 15-MAR-1985 00:00:00
Sales Clerk                   2          1        100         10
      10000

         3 Wash                 Georgia         G
1 Street14                     Barton               NJ 33333 3333 214
340-3333         12 02-JUL-1977 00:00:00 21-APR-2004 00:00:00
Designer                      1          2        100         10
      40000

         4 Bush                 Dave            D
56 Street                      Island               RI 44444 4444 215
777-4444         22 15-MAY-1945 00:00:00 02-AUG-1975 00:00:00
Designer                      1          2        100         10
      40000

         5 Will                 Robin           W
56 Street                      Island               MA 55555 5555 216
777-5555         25 10-DEC-1980 00:00:00 02-AUG-2007 00:00:00
Designer                      1          5        100         10
      40000

         6 Pete                 Mona            M
13 Ave                         York                 MO 66666 6666 217
111-6666          9 14-FEB-1966 00:00:00 15-MAR-1985 00:00:00
Sales Clerk                   2          5        100         10
      40000

         7 Roke                 John            J
67 Ave                         New York             BC 77777 7777 218
122-7777         10 14-JUN-1955 00:00:00 15-MAR-1975 00:00:00
Accountant                    3          2        100         10
      40000

         8 Horry                Tedi            T
1236 Lane                      Newton               NY 88888 8888 219
222-8888         13 10-JUN-1955 00:00:00 15-AUG-1985 00:00:00
Sales Representative          3          2        100         10
      50000

         9 Bar                  Candi           C
400 East Street                Yorken               NY 99999 9999 220
321-9999         12 10-OCT-1933 00:00:00 15-JAN-1969 00:00:00
Sales Representative          3          5        100         10
      35000


9 rows selected.

SQL> select * from department;
   DEPT_NO DEPT_NAME                MGR_NO
---------- -------------------- ----------
         1 Design                        1
         2 Sales                         1
         3 Development                   1

3 rows selected.

SQL>
SQL> select d.dept_name, e.lastname, o.order_date
  2  from department d, employee e, ord o
  3  where d.dept_no = e.dept_no
  4  and e.emp_no = o.emp_no
  5  /
DEPT_NAME            LASTNAME             ORDER_DATE
-------------------- -------------------- --------------------
Sales                Anderson             14-FEB-2002 00:00:00
Development          Roke                 14-FEB-2003 00:00:00
Sales                Last                 14-FEB-2004 00:00:00
Sales                Last                 14-FEB-1999 00:00:00
Sales                Last                 04-MAR-2002 00:00:00
Sales                Last                 07-APR-2003 00:00:00
Sales                Last                 20-JUN-2004 00:00:00
Design               Wash                 31-DEC-1999 00:00:00
Development          Roke                 26-DEC-2003 00:00:00
Sales                Last                 15-JUN-2008 17:39:15
Sales                Last                 16-JUN-2008 17:39:16
Sales                Last                 13-JUN-2008 17:39:17
Sales                Last                 16-JUN-2008 17:39:17

13 rows selected.

SQL>
SQL> drop table ord;

Table dropped.

SQL> drop table employee;

Table dropped.

SQL> drop table department;

Table dropped.

SQL>
SQL> --

   
    
  








Related examples in the same category

1.Join two tables: column is not mentioned in the SELECT list
2.Join with (+)
3.Use in operator with table join
4.Use column name alias during table join
5.Join and GROUP BY
6.Join three table to find out which employee sold that gift
7.Join using and join on
8.Join using clause
9.Join columns with full table name reference