Query to display Employee and Manager

I have to write a SQL Server query to display the employee last name and his respective manager’s name, as given below.

John works for Robert
Jane works for David

These are the schema for the two tables.

Employee Table

empno char (6)
firstname varchar(12)
midinit char(1)
lastname varchar(15)
workdept char(3)



Department Table 

deptno char(3)
deptname varchar(36)
mgrno char(6)
admrdept char (3)
location char(16)

Expected Output :

Adamson works for Stern 
Brown works for Stern 
Jefferson works for Pulaski 
Johnson works for Pulaski 
Jones works for Stern 
Lutz works for Stern 
Marino works for Pulaski 
Monteverde works for Pulaski 
Natz works for Kwan 

I tried this code but its not right

SELECT Concat(e.firstnme ," works for ", (select firstnme from Employee where mgrno = empno)) as Hierarchy
from Employee e
join Department d on e.workdept = d.deptno
order by lastname

Detailed Schema:

Schema

Answer

I guess something as simple as this should do it

SELECT e.LastName + ' works for ' + e2.LastName
from  Employee e 
  join Department d on e.workdept = d.deptno
  join Employee e2 on d.mgrno = e2.empno

How it works:

• The join from Employee e with Department d will get you exact 1 row in Department
• Then join back from this one Department to employee e2, this will return exact 1 employee (the boss) from Employee e2
• Now you have the employee in e, and the boss in e2

I tested with this

declare @Employee table (empno char(6), lastname varchar(15), workdept char(3))
declare @Department table (deptno char(3), mgrno char(6))

insert into @Employee (empno, lastname, workdept) values 
  ('123456', 'Adamson', 'dp1'), 
  ('123457', 'Brown', 'dp1'), 
  ('123458', 'Jefferson', 'dp2'), 
  ('123459', 'Johnson', 'dp2'),
  ('123460', 'Jones', 'dp1'),
  ('123461', 'Lutz', 'dp1'),
  ('123462', 'Marino', 'dp2'),
  ('123463', 'Monteverde', 'dp2'),
  ('123464', 'Natz', 'dp3'),
  ('123465', 'Stern', 'dp1'),
  ('123466', 'Pulaski', 'dp2'),
  ('123467', 'Kwan', 'dp3')

insert into @Department (deptno, mgrno) values ('dp1', '123465'), ('dp2', '123466'), ('dp3', '123467')

SELECT e.LastName + ' works for ' + e2.LastName
from  @Employee e 
  join @Department d on e.workdept = d.deptno
  join @Employee e2 on d.mgrno = e2.empno

the result

Adamson works for Stern
Brown works for Stern
Jefferson works for Pulaski
Johnson works for Pulaski
Jones works for Stern
Lutz works for Stern
Marino works for Pulaski
Monteverde works for Pulaski
Natz works for Kwan
Stern works for Stern
Pulaski works for Pulaski
Kwan works for Kwan

EDIT

if you want to leave out the bosses, add this where clause to the query

where  e.empno not in (select mgrno from @Department)

or alternative this where clause

where  e.empno <> e2.empno