List of all employees where I'm the manager of their department or some department above

Posted on 2012-04-03

Query Syntax

1 solution

Medium Priority

182 Views

Last Modified: 2012-04-04

Problem:
I want a list of all employees where I'm the manager of their department or some department above (in MS SQL)
I can be a manager of one of more departments

Table 1: EMPLOYEES: columns employeecode, name, departmentcode
Table 2: MANAGERS: columns employeecode, departmentcode (employeecode is manager of departmentcode)
Table 3: DEPARTMENTS: columns departmentcode, treecode
      treecode is a parent-child structure for each department, like
*TOP*parent*parent*parent*thisDepartment
      example: department 0310 has treecode *TOP*B05*B0003*0310*
      (0310's parent is B0003, B0003's parent is B05 and so on)

Example: if I am the manager of departments B05 and C01, I want all employees in any department having treecode LIKE '%*B05*%' or '%*C01*%' (MS SQL)

Comment

Question by:fmsol

2 Comments

LVL 23

Accepted Solution

by:wdosanjos

wdosanjos earned 2000 total points

ID: 378027482012-04-03

Please try the following:

Select * 
  From Employees
Where DepartmentCode in
    (Select d2.DepartmentCode From Managers m
        join Departments d1 on d1.DepartmentCode = m.DepartmentCode
        join Departments d2 on d2.TreeCode Like d1.TreeCode + '%'
        where m.EmployeeCode = <MyEmployeeCode>)

Open in new window

I hope this helps.

Author Closing Comment

by:fmsol

ID: 378050092012-04-04

Just a small change:
join Departments d2 on d2.TreeCode Like '%*' + d1.TreeCode + '*%'

Thank you!

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

Q&A with SQL Expert Jim Horn

Article by: Experts Exchange

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.

Stellar SQL Database Toolkit is Best Solution for All SQL Database Issues

Article by: Bharat

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…

Synchronize List Box in Access using SQL and VBA

Video by: crystal

With just a little bit of SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form. If you have never written code or looked at an SQL statement before, no problem! ... give i…

Expot Amazon Workmail Mailboxes to Outlook With Kernel Export Amazon WorkMail to PST

Video by: Sandeep

Go through the video which explain the procedure to export Amazon WorkMail single or multiple mailboxes to Outlook with Kernel Export Amazon WorkMail to PST tool. User can see the simple steps to export. It has salient features like filters (Include…