SQL - using an alias in a where clause in a subquery

Question

So this isn't actually my code, but just an example of what I'm trying to do. Ideally I'd be able to use INNER JOINS and foreign key relations to get data, but I can't in my real-life situation - this is just a simple example.

SELECT [EmployeeID],
       [DepartmentID],
       (SELECT Title FROM Depts WHERE ID = [DepartmentID]) AS Department, 
       (SELECT Name FROM DeptHeads WHERE DeptName = Department) AS DepartmentLead
FROM   Employees E

I'm getting data from one table (Employees).

I'm using one of the columns from that table (DepartmentID) in a where clause in a subquery, and creating an alias from that (Department)

I'm then trying to do the same thing as above, except using that alias in the where clause.

I get an error saying:

Invalid column name "Department"

Is there a better way for me to do this, or a way around this?

Solution 1

You can't use aliases you just defined. You can:

SELECT * FROM (

    SELECT [EmployeeID],
               [DepartmentID],
               (SELECT Title FROM Depts WHERE ID = [DepartmentID]) AS Department, 
               (SELECT Name FROM DeptHeads WHERE DeptName = Department) AS DepartmentLead
    FROM   Employees E

) Base

WHERE Base.Department = ...

Solution 2

;WITH MyCTE AS
(
    SELECT [EmployeeID],
       [DepartmentID],
       (SELECT Title FROM Depts WHERE ID = [DepartmentID]) AS Department, 
       (SELECT Name FROM DeptHeads WHERE DeptName = Department) AS DepartmentLead
    FROM   Employees E
)
SELECT *
FROM   MyCTE
WHERE  Department = 'IT'

Solution 3

Method 1:

SELECT [EmployeeID],
[DepartmentID],
[Department],
(SELECT Name FROM DeptHeads WHERE DeptName = Department) AS DepartmentLead
FROM
(SELECT [EmployeeID],
       [DepartmentID],
       (SELECT Title FROM Depts WHERE ID = [DepartmentID]) AS Department, 
       FROM   Employees E ) E2

Using [Department] alias in where clause

SELECT [EmployeeID],
[DepartmentID],
[Department],
(SELECT Name FROM DeptHeads WHERE DeptName = Department) AS DepartmentLead
FROM
(SELECT [EmployeeID],
       [DepartmentID],
       (SELECT Title FROM Depts WHERE ID = [DepartmentID]) AS Department, 
       FROM   Employees E ) E2
WHERE E2.Department = 'XYZ'

Method 2:

SELECT E.[EmployeeID],
       E.[DepartmentID],
       D.Title AS Department, 
       DH.Name AS DepartmentLead
FROM   Employees E
LEFT JOIN Depts D ON E.[DepartmentID] = D.ID
LEFT JOIN DeptHeads DH ON D.Title = DH.DeptName