Query to display the department names manager department name

user_10318088 0
Viewed 2590

I have to write a query to display Worker Department and its Manager Department from a table.

A Department cannot manage itself and all the departments should be displayed once with its Manager department.

This is the schema of the Employee and Department tables:

Employees:

empno char[6]

firstname varchar[12]

lastname varchar[15]

workdept char[3]

job char[9]


Department:

deptno char[3]

deptname varchar[36]

mgrno char[6]

admrdept char [3]

location char[16]

Am I missing something because I cant seem to do it.

This is the output I am expecting (Worker dept. and Manager Dept. are aliases):

Worker Dept.                            Manager Dept.
Administration Systems                  Development Center
Development Center                      Spiffy Computer Service
Information Center                      Spiffy Computer Service
Manufacturing Systems                   Development Center
Planning                                Spiffy Computer Service Div
Support Services                        Spiffy Computer Service Div

I have tried this but I cant get the manager Dept.:

SELECT distinct d.deptname,  d.location , d.admrdept  
FROM Department d 
JOIN Employee e on d.deptno = workdept

PS: I am getting the 3rd column as the dept. code according to the above query, how do I make the connection to the dept name.

sql-server-2008 tsql sql
1 Answers

Hard to tell based on the information given but I think you want:

select d.deptname as WorkerDept, md.deptname as ManagerDept
from Employee e
inner join Department d
   on (e.workdept = d.deptno)
inner join Department md
   on (d.admrdept = md.deptno)
where d.deptno != md.deptno
user_68329430
Related