SQL - Structured Query Language

Self Join



Self join is used to retrieve list of common columns matched data value records within a table with two different columns. The following self join is used to retrieve list of Employee and their Manager information that matched with Emp_Code and MGR_Code.

Note: For Demo, create EMP_Test Table and then INSERT sample records.

-- Example 37 --

 

USE EBusiness

GO

 

CREATE TABLE EMP_Test

(

Emp_Code numeric(5)Primary Key,

Emp_Name varchar(50) Not Null,

MGR_Code numeric(5) References EMP(Emp_Code)

)

GO

 

INSERT INTO EMP_Test VALUES (101,'Employee 101',NULL)

INSERT INTO EMP_Test VALUES (102,'Employee 102',101)

INSERT INTO EMP_Test VALUES (103,'Employee 103',101)

INSERT INTO EMP_Test VALUES (104,'Employee 104',101)

INSERT INTO EMP_Test VALUES (105,'Employee 105',101)

INSERT INTO EMP_Test VALUES (106,'Employee 106',NULL)

 

SELECT * FROM EMP_Test

 

SELECT EMP_Test.Emp_Code, Employee_Name = ET.Emp_Name, Manager_Name = EMP_Test.Emp_Name

FROM EMP_Test

INNER JOIN EMP_Test ET

ON EMP_Test.Emp_Code = ET.MGR_Code


Query Output Screen



* * * * *


Email Your Comment To AUTHOR