MySQL INNER join: Table Joining to itselfINNER 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.
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 reportNow 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
INNER JOIN with DISTINCT Query
To generate the manager table we have used this SQL ( List all the managers )
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
This article is written by plus2net.com team.