CODEWITHSUNDEEP
mysqlsql
Ander Juaristi
Ander Juaristi

Reputation: 328

Is there any way to replace column values when querying?

I've got a table that stores information about the employees:

mysql> select * from emp;

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-09-12 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.24 sec)

There's a column named MGR that says who's the boss of each employee (well, the boss' EMPNO).
What I want is to make a query that will show me all my employees and their bosses' NAMES, not IDs.
The resulting table would look something like the following:

+-------+----------+
| ENAME | BOSSNAME |
+-------+----------+
| SMITH |   FORD   |
| ALLEN |   BLAKE  |
|  etc. |    etc.  |
+-------+----------+

Is there any way to accomplish the task? Note that there's not a second table named BOSSES, or something like that. Every employee is stored in the same table.

Upvotes: 1

Views: 95

Answers (1)

John Woo
John Woo

Reputation: 263743

You need to join the table with itself using LEFT JOIN, so all employees without MGR will still be shown on the list

SELECT  a.Ename EmpName,
        b.Ename BossName
FROM    emp a
        LEFT JOIN emp b
            ON a.MGR = b.EmpNo

To further gain more knowledge about joins, kindly visit the link below:

OUTPUT

╔═════════╦══════════╗
║ EMPNAME ║ BOSSNAME ║
╠═════════╬══════════╣
║ SCOTT   ║ JONES    ║
║ FORD    ║ JONES    ║
║ ALLEN   ║ BLAKE    ║
║ WARD    ║ BLAKE    ║
║ MARTIN  ║ BLAKE    ║
║ TURNER  ║ BLAKE    ║
║ JAMES   ║ BLAKE    ║
║ MILLER  ║ CLARK    ║
║ ADAMS   ║ SCOTT    ║
║ JONES   ║ KING     ║
║ BLAKE   ║ KING     ║
║ CLARK   ║ KING     ║
║ SMITH   ║ FORD     ║
║ KING    ║ (null)   ║
╚═════════╩══════════╝

Upvotes: 7

Related Questions