CODEWITHSUNDEEP
sqljoinsubquery
gwhenning
gwhenning

Reputation: 138

SQL SubQuery from same data table

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?

Upvotes: 1

Views: 4916

Answers (3)

Ray K
Ray K

Reputation: 1490

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.

Upvotes: 3

Palpatim
Palpatim

Reputation: 9262

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

Upvotes: 2

Darren
Darren

Reputation: 792

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?

Upvotes: 0

Related Questions