SQL query in a query

Question

[Corporate confidential information or graphics removed]

I have this sort of structure in my database. I need to build a list of employees' names followed by names of their departments and offices. By now I've come to this query:

ALTER PROCEDURE FilterEmpList
    @empName nvarchar(250) = null,
    @empDepID int = null,
    @empOfficeID int = null,
    @empPosID int = null    
AS 
BEGIN
SELECT 
    E.emp_id AS 'emp_id',
    E.emp_name AS 'emp_name',      
    (SELECT pos_name FROM Positions WHERE pos_id=EP.pos_id) AS 'pos_name',
    (SELECT dep_name FROM Departments WHERE dep_id=OS.dep_id) AS 'dep_name',
    (SELECT office_name FROM Offices WHERE office_id=OS.office_id) AS 'office_name'
FROM 
    Org_Structure OS INNER JOIN (Emp_Positions EP INNER JOIN Employees E ON EP.emp_id=E.emp_id) ON OS.chain_id=EP.chain_id      
WHERE 
    (E.emp_name LIKE '%'+@empName+'%' OR @empName IS NULL)
    AND
    (OS.dep_id = @empDepID OR @empDepID IS NULL)
    AND
    (OS.office_id = @empOfficeID OR @empOfficeID IS NULL)
    AND
    (EP.pos_id = @empPosID OR @empPosID IS NULL)
END

When I execute it this way exec FilterEmpList, the error occurs: Invalid object name 'EP'.

Answer 1

Possible this be helpful for you -

ALTER PROCEDURE dbo.FilterEmpList
    @empName nvarchar(250) = null,
    @empDepID int = null,
    @empOfficeID int = null,
    @empPosID int = null    
AS 
BEGIN

    SELECT 
        E.emp_id,
        E.emp_name,      
        p.pos_name,
        d.dep_name,
        o.office_name
    FROM dbo.Org_Structure OS 
    JOIN dbo.Emp_Positions EP ON OS.chain_id=EP.chain_id  
    /*LEFT*/ JOIN dbo.Employees E ON EP.emp_id=E.emp_id 
    /*LEFT*/ JOIN dbo.Positions p ON p.pos_id=EP.pos_id
    /*LEFT*/ JOIN dbo.Departments d ON d.dep_id=OS.dep_id
    JOIN dbo.Offices o ON o.office_id=OS.office_id  
    WHERE (
              E.emp_name LIKE '%'+@empName+'%' 
            OR 
              @empName IS NULL
        )
        AND OS.dep_id = ISNULL(@empDepID, OS.dep_id)
        AND OS.office_id = ISNULL(@empOfficeID, OS.office_id)
        AND EP.pos_id = ISNULL(@empPosID, EP.pos_id)

END