MySQL INNER join: Table Joining to itself

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 tableManagersEmployee
idnamem_id
1John2
2Greek Tor1
3Alex John0
4Mike tour1
5Brain J3
6Ronald3
7Kin4
8Herod3
9Alen2
10Ronne1
idemp_name
2Greek Tor
3Alex John
1John
3Alex John
3Alex John
4Mike tour
3Alex John
2Greek Tor
1John
idemp_name
1John
2Greek Tor
3Alex John
4Mike tour
5Brain J
6Ronald
7Kin
8Herod
9Alen
10Ronne
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
1JohnGreek Tor
2Greek TorAlex John
4Mike tourJohn
5Brain JAlex John
6RonaldAlex John
7KinMike tour
8HerodAlex John
9AlenGreek Tor
10RonneJohn
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

Read More on Distinct Query .


CREATE TABLE IF NOT EXISTS `emp` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL DEFAULT '',
  `m_id` int(4) DEFAULT '0',
  UNIQUE KEY `id` (`id`)
) ;

-- Dumping data for table `emp`
--

INSERT INTO `emp` (`id`, `name`, `m_id`) VALUES
(1, 'John', 2), (2, 'Greek Tor', 3), (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);

Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here .





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer