Question: I've got the following SQL Statement:

```
select * from Leaves inner join LeaveDetails on Leaves.LeaveId= LeaveDetails.LeaveId 
inner join Employee on Leaves.EmployeeCode =  Employee.EmployeeCode
inner join LeaveType on Leaves.LeaveTypeId= LeaveType.LeaveTypeId 
inner join LeaveStatus on Leaves.StatusId = LeaveStatus.StatusId 
inner join Employee_organizationaldetails on Employee_organizationaldetails.EmployeeCode=Employee.EmployeeCode 
where Leaves.LeaveId = 7295 

```

Employee\_organizationdetails contains another column called reporting officer which is a foreign key to the same Employee table. Now I need to get the name of the Employee.

How can I write the above query so that I can get the name of the reporting officer as another column without fetching executing the query 

```
select (FirstName + ' ' + LastName) as name from Employee where EmployeeCode = ReportingTo

```

Here ReportingTo is the employee code. I need to join them vertically. Something similar to Union operator

Answer: 
You want to join back to another "copy" of the `Employee` table:

```
select *, (ro.FirstName + ' ' + LastName) as ReportingName
from Leaves inner join LeaveDetails on Leaves.LeaveId= LeaveDetails.LeaveId 
inner join Employee on Leaves.EmployeeCode =  Employee.EmployeeCode
inner join LeaveType on Leaves.LeaveTypeId= LeaveType.LeaveTypeId 
inner join LeaveStatus on Leaves.StatusId = LeaveStatus.StatusId 
inner join Employee_organizationaldetails on Employee_organizationaldetails.EmployeeCode=Employee.EmployeeCode  left outer join
Employee ro
on ro.EmployeeCode = ReportingTo
where Leaves.LeaveId = 7295;

```

You probably don't want the `*` -- I assume it is just a shorthand for the question. It is better to list columns explicitly, especially because there are duplicate column names.
Possible this be helpful for you -

```
SELECT 
      *
    , ReportingName = ro.FirstName + ' ' + LastName
FROM (
    SELECT *
    FROM dbo.Leaves l
    WHERE l.LeaveId = 7295
) l
JOIN dbo.LeaveDetails ld ON l.LeaveId = ld.LeaveId 
JOIN dbo.Employee e ON l.EmployeeCode =  e.EmployeeCode
JOIN dbo.LeaveType lt ON l.LeaveTypeId = lt.LeaveTypeId 
JOIN dbo.LeaveStatus ls ON l.StatusId = ls.StatusId 
JOIN dbo.Employee_organizationaldetails e2 ON e2.EmployeeCode = e.EmployeeCode  
LEFT JOIN dbo.Employee ro ON ro.EmployeeCode = ReportingTo

```