Query to display Employee and Manager

Question

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 1

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

Answer 2

I guess you need to concatenate the employee's last name and the manager they are working for. If I understood correctly, you can try this query

select Concat(e.lastname, " works for ", f.lastname) as stmt 
from Employee e 
  join Department d on e.workdept = d.deptno 
  join (select distinct lastname, workdept 
        from Employee e 
          join Department d on e.workdept = d.deptno and mgrno = empno
       ) f on f.workdept = d.deptno order by lastname".

• First join is to connect the employee table with the department table for getting the employee last name
• second join is equivalent to substring to achieve manager name from same employee table for different criteria "mrgno = empno" helps to filtered out with respect to department table along with deptno column.

This can be reduced to

select Concat(e.lastname, " works for ", f.lastname) as stmt 
from Employee e 
  join Department d on e.workdept = d.deptno 
  join Employee f on f.workdept = e.workdept and d.mgrno = f.empno order by lastname".

I guess my second solution might solve the complications.

Answer 3

I think this will work

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
      order by e.lastname

Result:

Hierarchy

Adamson works for Stern
Brown works for Stern
Geyer works for Geyer
Jefferson works for Pulaski
Johnson works for Pulaski
Jones works for Stern
Kwan works for Kwan
Lutz works for Stern
Marino works for Pulaski
Monteverde works for Pulaski
Natz works for Kwan
Nicholls works for Kwan
Perez works for Pulaski
Pianka works for Stern
Pulaski works for Pulaski
Quintana works for Kwan
Scoutten works for Stern
Smith works for Pulaski
Stern works for Stern
Thompson works for Thompson
Walker works for Stern
Yamamoto works for Stern