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'.

score 1 · Answer 1 · answered Jun 04 '13 at 10:01

You can not reference inline select table out of its scope . I suggest to use natural join like :

Select Emp_ID,Emp_Name,Dep_Name,Office_Name,Pos_Name
From Employees e
Join Emp_positions ep on ep.Emp_ID = e.Emp_ID
Join Org_Structue os on os.Chain_ID = ep.Chain_ID
Join Departments d on d.Dep_ID = os.Dep_ID
Join Offices o on o.office_ID = os.Office_ID
Join Positions p on p.Pos_ID = os.Pos_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)

score 1 · Answer 2 · answered Jun 04 '13 at 10:02

There is a problem with you inner join/joins. After seeing th query and the ERD I am assuming you are lookin for this,

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 ON EP.ChainId ==  Emp_Positions.ChainId
INNER JOIN Employees E ON EP.emp_id=E.emp_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

Also try to make the Aliases more readible.

Thanks

Devart · Accepted Answer · 2013-06-04T10:49:48.290

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

Steph Locke · Answer 4 · 2013-06-04T15:28:52.107

0

Some of you code has gotten misplaced I think. Try this...

SELECT 
    E.emp_id AS 'emp_id',
    E.emp_name AS 'emp_name',      
    P.pos_name  AS 'pos_name',
    D.dep_name AS 'dep_name',
    O.office_name AS 'office_name'
FROM 
    Org_Structure OS 
    INNER JOIN Emp_Positions EP ON OS.chain_id=EP.chain_id 
    INNER JOIN Employees E ON EP.emp_id=E.emp_id    
    LEFT JOIN Positions P ON P.pos_id=EP.pos_id
    LEFT JOIN Departments D ON D.dep_id=OS.dep_id
    LEFT JOIN Offices O ON O.office_id=OS.office_id
WHERE 
    (E.emp_name LIKE '%'+@empName+'%' OR @empName IS NULL)
    AND
    (OS.dep_id = @empDepID OR @empDepID IS NULL)
    AND
    (COALESCE(O.office_id,@empOfficeID ) = @empOfficeID OR @empOfficeID IS NULL)
    AND
    (EP.pos_id = @empPosID OR @empPosID IS NULL)

edited Jun 04 '13 at 15:28

answered Jun 04 '13 at 09:56

Steph Locke

5,951
4
39
77

@Vladimir I've moved the subqueries out of the SELECT as the reference to EP inside oen of them may have been the issue – Steph Locke Jun 04 '13 at 10:43
@Vladimir I amended to show how a coalesce can be used to keep NULLs around - you may want to add a further COALESCE into the SELECT to put 'No Office' or something where it is missing depending on what you're trying to achieve – Steph Locke Jun 04 '13 at 12:29
@Devart's ISNULL clauses are cleaner than mine - I recommend those – Steph Locke Jun 04 '13 at 15:37
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/31235/discussion-between-steph-locke-and-vladimir) – Steph Locke Jun 05 '13 at 08:09

score 0 · Answer 5 · answered Jun 04 '13 at 10:02

Your query will fail because you are trying to join EP to Org_structure table but this EP is inside the brackets. This is not allowed. Try below

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 ON OS.chain_id=EP.chain_id 
  INNER JOIN Employees E ON EP.emp_id=E.emp_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

score 0 · Answer 6 · answered Jun 04 '13 at 10:24

0

hi have you checked that your table EMP_Positions exists ?

answered Jun 04 '13 at 10:24

SQL query in a query

6 Answers6