Using table alias to qualify column name when column names exist

        
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, 0 rows affected (0.00 sec)

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

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

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

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

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

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

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

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

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

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

mysql>
mysql>
mysql> SELECT   PAYMENTNO, PEN.EmployeeNO, AMOUNT,
    ->          NAME, INITIALS
    -> FROM     PENALTIES AS PEN, EmployeeS AS P
    -> WHERE    PEN.EmployeeNO = P.EmployeeNO;
+-----------+------------+--------+---------+----------+
| PAYMENTNO | EmployeeNO | AMOUNT | NAME    | INITIALS |
+-----------+------------+--------+---------+----------+
|         1 |          6 | 100.00 | Link    | R        |
|         2 |         44 |  75.00 | Baker   | E        |
|         3 |         27 | 100.00 | Collins | DD       |
|         4 |        104 |  50.00 | Jane    | D        |
|         5 |         44 |  25.00 | Baker   | E        |
|         6 |          8 |  25.00 | Mary    | B        |
|         7 |         44 |  30.00 | Baker   | E        |
|         8 |         27 |  75.00 | Collins | DD       |
+-----------+------------+--------+---------+----------+
8 rows in set (0.00 sec)

mysql>
mysql> SELECT   PAYMENTNO, PEN.EmployeeNO, AMOUNT,
    ->          NAME, INITIALS
    -> FROM     PENALTIES PEN, EmployeeS P
    -> WHERE    PEN.EmployeeNO = P.EmployeeNO;
+-----------+------------+--------+---------+----------+
| PAYMENTNO | EmployeeNO | AMOUNT | NAME    | INITIALS |
+-----------+------------+--------+---------+----------+
|         1 |          6 | 100.00 | Link    | R        |
|         2 |         44 |  75.00 | Baker   | E        |
|         3 |         27 | 100.00 | Collins | DD       |
|         4 |        104 |  50.00 | Jane    | D        |
|         5 |         44 |  25.00 | Baker   | E        |
|         6 |          8 |  25.00 | Mary    | B        |
|         7 |         44 |  30.00 | Baker   | E        |
|         8 |         27 |  75.00 | Collins | DD       |
+-----------+------------+--------+---------+----------+
8 rows in set (0.00 sec)

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

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

mysql>

Related examples in the same category

1.	Using More Than one Table
2.	Self join
3.	Simple table join
4.	Join three tables
5.	Query data from two tables
6.	JOIN two tables with alias name
7.	Using a Join to Control Query Output Order
8.	Using a Join to Create a Lookup Table from Descriptive Labels
9.	Return the first names and surnames of both the sales rep and the customer, as well as the value of the sale
10.	Query data from two tables 2
11.	Finding Rows in One Table That Match Rows in Another
12.	Identify records from author table that corresponds to the author name, use its a_id value to find matching re
13.	Using information in the book table to find information in the author table
14.	Finding Rows with No Match in Another Table
15.	Shorten the output column list to include only columns from the author table
16.	List each author from the author table, and whether or not you have any books by the author
17.	Using table alias to qualify column name
18.	PSEUDONYMS FOR TABLE NAMES
19.	Qualify the column name with table name

Using table alias to qualify column name when column names exist : Simple JOIN « Join « SQL / MySQL

Related examples in the same category