Inner join of single table to itself to get employ manager relationship MySQL tables

INNER join SQL command is mostly used to join one table to it self. The biggest advantage of doing this is to get linking information from the same table.

The best example of INNER join will be employee table where we will keep the employee and its manager as a single record. This way by linking to the table it self we will generate a report displaying it as two linked tables. Each record will have one additional field storing the data of the manager by keeping the employee ID and we will use M_ID ( manager ID ) to link with main employee ID. This way we will link two virtual tables generated from one main table. Here is the table. You can download /copy the sql dump file to create your own MySQL table for testing.

Main table

Managers

Employee

id	name	m_id
1	John	2
2	Greek Tor	1
3	Alex John	0
4	Mike tour	1
5	Brain J	3
6	Ronald	3
7	Kin	4
8	Herod	3
9	Alen	2
10	Ronne	1

id	emp_name
2	Greek Tor
3	Alex John
1	John
3	Alex John
3	Alex John
4	Mike tour
3	Alex John
2	Greek Tor
1	John

id	emp_name
1	John
2	Greek Tor
3	Alex John
4	Mike tour
5	Brain J
6	Ronald
7	Kin
8	Herod
9	Alen
10	Ronne

Main Table (emp ): Table with id , name and m_id. Each employ has one unique id and one m_id ( manager id which is part of id field )

Note that we have only one table main table and other two Managers and Employee reports are generated out of the main table only.

In the table you can see every record has one manager id field known as m_id. We have used the unique id of the employee in the m_id field to mark who is the manager for the employee.

Employee Manager report

Now let us use inner join to create one report to display who is the manager of which employee. Check this SQL

SELECT t1.id, t1.name as emp_name, t2.name as manager FROM emp as t1 
INNER JOIN emp as t2 on t2.id = t1.m_id

id	emp_name	manager
1	John	Greek Tor
2	Greek Tor	Alex John
4	Mike tour	John
5	Brain J	Alex John
6	Ronald	Alex John
7	Kin	Mike tour
8	Herod	Alex John
9	Alen	Greek Tor
10	Ronne	John

Note : Here record with id=3 is missing as Alex John does not have any Manager.

INNER JOIN with DISTINCT Query

To generate the manager table we have used this SQL ( List all the managers )

SELECT DISTINCT(t1.id),t1.name as emp_name from emp as t1 INNER JOIN emp as t2 on t1.id=t2.m_id

MySQL INNER join: Table Joining to itself

Employee Manager report

INNER JOIN with DISTINCT Query

Subscribe