Self join in mysql employee manager example
In this blog post, we will learn self join of MySQL using the employee and manager example. In other words, A self join involves using a single table twice in a query, with each instance assigned a different alias. This enables comparisons or relationships between different rows within the same table.
In table we will create a column manager_id which specifies the manager of an employee and we will compare the employees manager_id column with id column of another instance of employee table.
Create employees table
CREATE TABLE `employees` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`manager_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `manager_id` (`manager_id`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (manager_id) REFERENCES employees(id)
);
Insert data into employees table
insert into employees (name, manager_id) values('John', null), ('Diana', 1), ('David', 1);
Output :
Query OK, 3 rows affected (0.38 sec) Records: 3 Duplicates: 0 Warnings: 0
Self Join Statment to get Employees and their manager name
select e.id, e.name , m.name as manager from employees e left join employees m on e.manager_id = m.id;
Output
+----+-------+---------+ | id | name | manager | +----+-------+---------+ | 1 | John | NULL | | 2 | Diana | John | | 3 | David | John | +----+-------+---------+ 3 rows in set (0.00 sec)
If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.
Don't forget to share this article! Help us spread the word by clicking the share button below.
We appreciate your support and are committed to providing you valuable and informative content.
We are thankful for your never ending support.
Random Code Snippet Queries: Sql
- SQL query to delete all rows older than 30 days
- How to check the MySQL version
- Clear the terminal screen in MySQL within the Windows powershell
- Get comma separated ids from table using MySQL
- How to get a list of MySQL views?
- Drop foreign id column (user_id) in MySQL
- Create Index in MySQL with example
- Cannot drop index 'fk_role_id': needed in a foreign key constraint
- Get data in random order with limit from table using mysql
- How to check column exists or not in table using MySQL
- Insert multiple rows in a single MySQL query
- How to Rename column name in MySql ?
- ERROR 3780 (HY000): Referencing column 'order_id' and referenced column 'id' in foreign key constraint 'order_items_ibfk_1' are incompatible.
- Difference between SQL and NoSQL databases
- How to create view in MySQL database
- Get create table Query from existing table in phpMyAdmin
- Get number of connections being used by a specific user in MySQL
- How to drop function in MySQL ?
- MySQL create procedure example
- How to get yesterday's date in MySQL ?
- Create Employee table in MySQL
- Get full name of a person in SQL using function
- How to retrieve data from two tables with one SQL statement
- SQL query to delete records older than 6 months
- Database connection using mySQLi with object-oriented