Examples of Self JOIN
Hello friends,
Self Join is very basic thing in SQL.I am explaining it with example in SQL Server
Enjoy Self JOIN
Step 1 :Create a table named as employee Run the following Query
CREATE TABLE [dbo].[employee](
[empId] [int] NOT NULL,
[empname] [nvarchar](1200) NOT NULL,
[managerid] [int] NOT NULL
) ON [PRIMARY]
Step 2 : Insert data into it
Insert into employee values (1,'john',1)
Insert into employee values (2,'Jasmine',1)
Insert into employee values (3,'Rahman',2)
Insert into employee values (4,'Victor',3)
Insert into employee values (5,'Majid',1)
Insert into employee values (6,'Olvin',3)
Insert into employee values (7,'Soniya',2)
step 3 : Now ,I want the records of employees with thier manager Id and manager name
so I will apply Self JOIN on employee table on empId and managerId.
Check and run this Query
SELECT a.empid,a.empname as EmployeeName,a.managerid as ManagerId,b.empName as ManagerName FROM
employee a INNER JOIN employee b
ON b.empid=a.managerid
Output will seen like as
EmpId empname managerid ManagerName
1 john 1 john
2 Jasmine 1 john
3 Rahman 2 Jasmine
4 Victor 3 Rahman
5 Majid 1 john
6 Olvin 3 Rahman
7 Soniya 2 Jasmine
Any Suggestions will be appreciated ! :)
Thanks
Shubahnk Upadhyay
Self Join is very basic thing in SQL.I am explaining it with example in SQL Server
Enjoy Self JOIN
Step 1 :Create a table named as employee Run the following Query
CREATE TABLE [dbo].[employee](
[empId] [int] NOT NULL,
[empname] [nvarchar](1200) NOT NULL,
[managerid] [int] NOT NULL
) ON [PRIMARY]
Step 2 : Insert data into it
Insert into employee values (1,'john',1)
Insert into employee values (2,'Jasmine',1)
Insert into employee values (3,'Rahman',2)
Insert into employee values (4,'Victor',3)
Insert into employee values (5,'Majid',1)
Insert into employee values (6,'Olvin',3)
Insert into employee values (7,'Soniya',2)
step 3 : Now ,I want the records of employees with thier manager Id and manager name
so I will apply Self JOIN on employee table on empId and managerId.
Check and run this Query
SELECT a.empid,a.empname as EmployeeName,a.managerid as ManagerId,b.empName as ManagerName FROM
employee a INNER JOIN employee b
ON b.empid=a.managerid
Output will seen like as
EmpId empname managerid ManagerName
1 john 1 john
2 Jasmine 1 john
3 Rahman 2 Jasmine
4 Victor 3 Rahman
5 Majid 1 john
6 Olvin 3 Rahman
7 Soniya 2 Jasmine
Any Suggestions will be appreciated ! :)
Thanks
Shubahnk Upadhyay
Comments
Post a Comment