SQL SubQuery from same data table

Question

I'm having trouble wrapping my head around what should be a pretty simple query in MS SQL. I have two tables: Employees and Departments.

Employees consists of the standard items: ID (pkey int), FName, LName, ... , DepartmentID.

Departments consists of DepartmentID, DepartmentName, ... , ManagerID.

There is a relationship from Employees.DepartmentID to Departments.DepartmentID, and a relationship between Departments.ManagerID and Employees.EmployeeID.

In other words, each employee has a department, and each department has a manager that is also an employee.

I'm trying to create a view that will display the employee name, ... , department, and department manager.

I keep getting an error that more than one value is being returned when using this code:

SELECT
Employees_1.EmployeeID, Employees_1.FirstName, Employees_1.LastName, 
Departments_1.DepartmentName,
(SELECT
    dbo.Employees.LastName
    FROM dbo.Employees 
    INNER JOIN dbo.Departments
      ON dbo.Departments.DepartmentManager = dbo.Employees.EmployeeID
) AS ManagerName
FROM dbo.Employees AS Employees_1
INNER JOIN dbo.Departments AS Departments_1 
  ON Employees_1.Department = Departments_1.DepartmentID 
  AND Employees_1.EmployeeID = Departments_1.DepartmentManager

Any ideas on how to join back to the same table (different row) based on the relationship from a second table?

score 3 · Accepted Answer · edited May 23 '17 at 12:32

3

I would advise against the subquery (they are much more expensive) in favor of another join. This second join will go from the dept table back to the emp table on the department manager ID to the employee's Employee ID.

(I added the column aliases in for clarity - they are in no way needed)

SELECT
    emp.EmployeeID          "EmpID",
    emp.FirstName           "EmpFirst",
    emp.LastName            "EmpLast",
    dept.DepartmentName     "DeptName",
    deptMgrEmp.FirstName    "MgrFirst",
    deptMgrEmp.LastName     "MgrLast"
FROM
    dbo.Employees as emp
    LEFT JOIN dbo.Departments as dept
    on emp.DepartmentID = dept.DepartmentID
    LEFT JOIN dbo.Employees as deptMgrEmp
    on dept.ManagerID = deptMgrEmp.EmployeeID

Here is a good thread discussing JOIN vs Subquery link.

edited May 23 '17 at 12:32

Community

1
1

answered Oct 29 '12 at 18:29

Ray K

1,452
10
17

Use the Left (outer) join to make sure all employees are displayed regardless if they are assigned a dept / manager or not. Use Inner if you want to hide employees/departments that are not associated further. – Ray K Oct 29 '12 at 18:31
@Ray K Thank you, this gets me off to a good start. Something is dreadfully wrong in my code as I'm getting the wrong names (if at all). MS SQL's query builder replaces your Left JOINs with LEFT OUTER JOINs, and I left the column aliases out since you mentioned they might be unnecessary. In my query, I have the keys show up, and they populate over correctly, but in my query results, where the department manager's name should be I get a list of employees by employee ID. (e.g. -1st employee deptmgrID=2, shows NULL. 2nd employee deptmgrID=2, shows employee 1's Name. Third employee ID=2, 2's Name) – gwhenning Oct 29 '12 at 23:44

Palpatim · Answer 2 · 2012-10-29T18:36:36.323

2

SELECT
  e.EmployeeId,
  e.FirstName,
  e.LastName,
  d.DepartmentName,
  m.EmployeeId as ManagerId
  m.FirstName as ManagerFirstName,
  m.LastName as ManagerLastName
FROM
  dbo.Employees e 
  JOIN dbo.Departments d
    ON d.DepartmentId = e.DepartmentId
  JOIN dbo.Employees m
    ON d.DepartmentManager = m.EmployeeID

NOTE: Untested code, but the idea is that you can simply alias the Employees table as "m" (for Managers) and join back to it, keyed on the manager's EmployeeId

edited Oct 29 '12 at 18:36

answered Oct 29 '12 at 18:28

Palpatim

9,074
35
43

I noticed that it also changes SELECT dbo.Employees.EmployeeID to SELECT TOP (100) PERCENT dbo.Employees.EmployeeID. – gwhenning Oct 29 '12 at 23:50

score 0 · Answer 3 · answered Oct 29 '12 at 18:24

0

In your select clause

(SELECT dbo.Employees.LastName FROM dbo.Employees INNER JOIN dbo.Departments ON dbo.Departments.DepartmentManager = dbo.Employees.EmployeeID)

you need to filter by department id.

As it stands, it will return all the managers for all departments?

answered Oct 29 '12 at 18:24

Darren

722
4
12

@Ray K -Disregard earlier comment, copied your code, changed one or two variable names, and it worked. Oddly, I had simply used the GUI to select the fields I wanted, and then modified the code to look like yours. Thank you again! – gwhenning Oct 30 '12 at 00:00

SQL SubQuery from same data table

3 Answers3