Solved

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

Posted on 2012-04-03
2
173 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)
0
Comment
Question by:fmsol
2 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 37802748
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.
0
 

Author Closing Comment

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

Thank you!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query 8 48
SQL Syntax 5 34
Oracle Query - Return results based on minimum value 8 31
T-SQL: New to using transactions 9 20
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question