Avoid referring table two times in the WHERE clause

Question

Following is a simplified version of my database in SQL Server 2005. I need to select employees based on business units. Each employee has home department, parent department and visiting department. Based on the department, business unit can be found out.

• For an employee, if the HomeDeptID = ParentDeptID, then @SearchBusinessUnitCD should be present for the VisitingDeptID.
• If HomeDeptID <> ParentDeptID, then @SearchBusinessUnitCD should be present for the ParentDeptID.

Following query works fine. But it has scan on the #DepartmentBusinesses table two times. Is there a way to use the table #DepartmentBusinesses only once by making it as a CASE statement or similar?

DECLARE @SearchBusinessUnitCD CHAR(3)
SET @SearchBusinessUnitCD = 'B'

--IF HomeDeptID = ParentDeptID, then @SearchBusinessUnitCD should be present for the VisitingDeptID
--IF HomeDeptID <> ParentDeptID, then @SearchBusinessUnitCD should be present for the ParentDeptID

CREATE TABLE #DepartmentBusinesses (DeptID INT, BusinessUnitCD CHAR(3))
INSERT INTO #DepartmentBusinesses
    SELECT 1, 'A' UNION ALL 
    SELECT 2, 'B' 

CREATE NONCLUSTERED INDEX IX_DepartmentBusinesses_DeptIDBusinessUnitCD ON #DepartmentBusinesses (DeptID,BusinessUnitCD)

DECLARE @Employees TABLE (EmpID INT, HomeDeptID INT, ParentDeptID INT, VisitingDeptID INT)
INSERT INTO @Employees 
    SELECT 1, 1, 1, 2 UNION ALL
    SELECT 2, 2, 1, 3

SELECT * 
FROM @Employees
WHERE
    (
            HomeDeptID = ParentDeptID
        AND
            EXISTS (
                SELECT 1
                FROM #DepartmentBusinesses
                WHERE DeptID = VisitingDeptID
                    AND BusinessUnitCD = @SearchBusinessUnitCD)
            )
    OR 
    (
            HomeDeptID <> ParentDeptID
        AND
            EXISTS (
                SELECT 1
                FROM #DepartmentBusinesses
                WHERE DeptID = ParentDeptID
                    AND BusinessUnitCD = @SearchBusinessUnitCD
            )
    )

DROP TABLE #DepartmentBusinesses

Plan

Answer 1

SELECT * 
FROM @Employees e
WHERE EXISTS (
        SELECT 1
        FROM #DepartmentBusinesses t
        WHERE t.BusinessUnitCD = @SearchBusinessUnitCD
            AND (
                (e.HomeDeptID = e.ParentDeptID AND t.DeptID = e.VisitingDeptID)
                OR
                (e.HomeDeptID != e.ParentDeptID AND t.DeptID = e.ParentDeptID)
            )
    )