Custom Search
 


Using Self Joins to Combine Data from the Same Table


´╗┐When you join a table to itself on columns with common values, you can picture how each record is related to one another. This is known as self-join.

Self-join is normally used to represent hierarchical relationship or tree structure in a table. In Northwind employees table, an employee has a manager who is also an employee. Every employee has a ReportsTo value which stores the EmployeeID of employee's manager.

In employees table, EmployeeID is primary key and ReportsTo is foreign key which relates back to EmployeeID in the same table. So we can use ReportsTo and EmployeeID to join the employees table to itself and find out the manager for each employee.

Practice #1: Use self-join for employees table.

Copy and paste the following SQL to your SQLyog free Community Edition query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query displays manager and staff relationship.

The query uses self-join where employees table
joined with itself.

Joined columns:

Foreign key column ReportsTo in the employees table
which is aliased as staff table.

Primary key column EmployeeID in the employees table
which is aliased as manager table.
*/
select manager_tbl.FirstName as Manager,
staff_tbl.FirstName as Staff
from employees as staff_tbl
inner join employees as manager_tbl
on staff_tbl.ReportsTo=manager_tbl.EmployeeID;

Query result set - 8 rows returned:
Using self join.

Practice #2: Use self-join for orders table.

Copy and paste the following SQL to your SQLyog free Community Edition query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query displays a list of employee IDs who serviced
the same customers. The where clause eliminates duplicate
rows where emp1 is equal to emp2.

Note that the first row and the second row are actually
the same. This kind of duplication is removed in Practice #3.
*/
select distinct a.EmployeeID as emp1,
a.CustomerID,
b.EmployeeID as emp2
from orders as a
inner join orders as b
on a.CustomerID=b.CustomerID
where a.EmployeeID <> b.EmployeeID
order by a.CustomerID, a.EmployeeID;

Query result set - 2238 rows returned:
Using self join.

Practice #3: Use self-join for orders table.

Copy and paste the following SQL to your SQLyog free Community Edition query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query displays a list of employee IDs who serviced
the same customers. The where clause eliminates all kinds
of duplicated rows.

Note that this query fixed the problem in Practice #2 where
mirrored records existed for row 1 and row 2.
*/
select distinct a.EmployeeID as emp1,
a.CustomerID,
b.EmployeeID as emp2
from orders as a
inner join orders as b
on a.CustomerID=b.CustomerID
where a.EmployeeID > b.EmployeeID
order by a.CustomerID, a.EmployeeID;

Query result set - 1119 rows returned:
Using self join.





Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Using UNION to Append Result Sets

Back to Tutorial Index Page


Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy