Q:

Vertical Join in an SQL Statement

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

A:

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.

