Join three table together : Join Select « Join « SQL / MySQL

Home
SQL / MySQL
1.Aggregate Functions
2.Backup Load
3.Command MySQL
4.Cursor
5.Data Type
6.Database
7.Date Time
8.Engine
9.Event
10.Flow Control
11.FullText Search
12.Function
13.Geometric
14.I18N
15.Insert Delete Update
16.Join
17.Key
18.Math
19.Procedure Function
20.Regular Expression
21.Select Clause
22.String
23.Table Index
24.Transaction
25.Trigger
26.User Permission
27.View
28.Where Clause
29.XML
SQL / MySQL » Join » Join Select 
Join three table together
    
mysql>
mysql> CREATE TABLE EmployeeS(
    ->          EmployeeNO       INTEGER      NOT NULL,
    ->          NAME           CHAR(15)     NOT NULL,
    ->          INITIALS       CHAR(3)      NOT NULL,
    ->          BIRTH_DATE     DATE                 ,
    ->          SEX            CHAR(1)      NOT NULL,
    ->          JOINED         SMALLINT     NOT NULL,
    ->          STREET         VARCHAR(30)  NOT NULL,
    ->          HOUSENO        CHAR(4)              ,
    ->          POSTCODE       CHAR(6)              ,
    ->          TOWN           VARCHAR(30)  NOT NULL,
    ->          PHONENO        CHAR(13)             ,
    ->          LEAGUENO       CHAR(4)              ,
    ->          PRIMARY KEY    (EmployeeNO)           );
Query OK, rows affected (0.01 sec)

mysql> INSERT INTO EmployeeS VALUES (2'Jack', 'R''1948-09-01', 'M'1975'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (6'Link', 'R''1964-06-25', 'M'1977'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (7'Wise', 'GWS', '1963-05-11', 'M'1981'First Way','39', '9758VB', 'Stratford', '070-347689', NULL);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (8'Mary', 'B''1962-07-08', 'F'1980'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (27'Collins', 'DD', '1964-12-28', 'F'1983'Long DRay','804', '8457DK', 'Eltham', '079-234857', '2513');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (28'Collins', 'C''1963-06-22', 'F'1983'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (39'Bishop', 'D''1956-10-29', 'M'1980'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (44'Baker', 'E''1963-01-09', 'M'1980'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (57'Brown', 'M''1971-08-17', 'M'1985'First Way','16', '4377CB', 'Stratford', '070-473458', '6409');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (83'Hope', 'PK', '1956-11-11', 'M'1982'Main Road','16A', '1812UP', 'Stratford', '070-353548', '1608');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (95'Miller', 'P''1963-05-14', 'M'1972'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (100'Link', 'P''1963-02-28', 'M'1979'Haseltine Lane','80', '6494SG', 'Stratford', '070-494593', '6524');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (104'Jane', 'D''1970-05-10', 'F'1984'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (112'Bailey', 'IP', '1963-10-01', 'F'1984'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319');
Query OK, row affected (0.00 sec)

mysql>
mysql> CREATE   TABLE PENALTIES
    ->         (PAYMENTNO      INTEGER      NOT NULL,
    ->          EmployeeNO       INTEGER      NOT NULL,
    ->          PAYMENT_DATE   DATE         NOT NULL,
    ->          AMOUNT         DECIMAL(7,2NOT NULL,
    ->          PRIMARY KEY    (PAYMENTNO)          );
Query OK, rows affected (0.00 sec)

mysql>
mysql> INSERT INTO PENALTIES VALUES (1,  6'1980-12-08',100);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (244'1981-05-05', 75);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (327'1983-09-10',100);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (4,104'1984-12-08', 50);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (544'1980-12-08', 25);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (6,  8'1980-12-08', 25);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (744'1982-12-30', 30);
Query OK, row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (827'1984-11-12', 75);
Query OK, row affected (0.00 sec)

mysql>
mysql>
mysql> CREATE   TABLE TEAMS
    ->         (TEAMNO         INTEGER      NOT NULL,
    ->          EmployeeNO       INTEGER      NOT NULL,
    ->          DIVISION       CHAR(6)      NOT NULL,
    ->          PRIMARY KEY    (TEAMNO)             );
Query OK, rows affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO TEAMS VALUES (1,  6'first');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO TEAMS VALUES (227'second');
Query OK, row affected (0.00 sec)

mysql>
mysql> SELECT   PAYMENTNO, NAME
    -> FROM     PENALTIES, EmployeeS, TEAMS
    -> WHERE    PENALTIES.EmployeeNO = TEAMS.EmployeeNO
    -> AND      TEAMS.EmployeeNO = EmployeeS.EmployeeNO;
+-----------+---------+
| PAYMENTNO | NAME    |
+-----------+---------+
|         | Link    |
|         | Collins |
|         | Collins |
+-----------+---------+
rows in set (0.00 sec)

mysql>
mysql> drop table teams;
Query OK, rows affected (0.00 sec)

mysql> drop table Employees;
Query OK, rows affected (0.00 sec)

mysql> drop table penalties;
Query OK, rows affected (0.00 sec)

   
    
    
    
  
Related examples in the same category
1.Simple Join two tables
2.JOINs Across Two Tables
3.JOINs Across Two Tables with link id
4.JOINs Across Three or More Tables
5.Table joins and where clause
6.Select distinct column value during table join
7.Select distinct column values in table join
8.Count joined table
9.Performing a Join Between Tables in Different Databases
10.LINESTRING type column: One or more linear segments joining two points; one-dimensional.
11.Select other columns from rows containing a minimum or maximum value is to use a join.
12.Retrieve the overall summary into another table, then join that with the original table:
13.Tests a different column in the book table to find the initial set of records to be joined with the author tab
14.Select the maximum population value into a temporary table, Then join the temporary table to the original one
15.Creating a temporary table to hold the maximum price, and then joining it with the other tables:
16.To display the authors by name rather than ID, join the book table to the author table
17.To display the author names, join the result with the author table
18.The summary is written to a temporary table, which then is joined to the cat_mailing table to produce the reco
19.Addition of a WHERE clause for table join
20.Join more than two tables together.
21.Creating Straight Joins: STRAIGHT_JOIN
22.Use the basic join syntax and you specify the STRAIGHT_JOIN table option in the SELECT clause
23.Creating Natural Joins
24.Joining Columns with CONCAT
25.A basic join
26.Rewriting Sub-selects as Joins
27.Display game code, name, price and vendor name for each game in the two joined tables
28.Get the player number, the sex, and the name of each player who joined the club after 1980.
29.Join two tables with char type columns
30.Convert subqueries to JOINs
31.Join with another database
32.Join two tables with shared columns values
33.Qualify column name with table name during the table join
34.Three tables join together
35.Compare date type value during table join
36.Join on syntax
37.Natural join syntax
38.Join with Integer type column
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.