I have a table called Employees

I want to run a query where it should return Wage employees. I want to also include those Wage employees' managers in the query so it should return something like this:

Querying the Wage employees is easy enough though wondering if you can help me with the query so that it also includes their managers in the list:

Select * From Employees
Where [Employment Rate] = 'Wage'

CodePudding user response：

If you use a CTE to determine the relevant employees you can then union on a query to obtain their managers e.g.

WITH cte AS (
    SELECT * 
    FROM Employees
    WHERE [Employment Rate] = 'Wage'
)
SELECT * FROM cte
UNION ALL
SELECT * FROM Employees WHERE Id IN (SELECT [Manager ID] FROM cte);

CodePudding user response：

You could use a sub query like like this

Select * 
From Employees
Where [Employment Rate] = 'Wage'
   or ID in (SELECT [Manager ID] FROM Employees Where [Employment Rate] = 'Wage')

You could also use a UNION statement -- which I like better because I would add a new column in there showing the source of the data -- in my experience you always want this when working with data in this way:

SELECT 'Worker' as SOURCE,
       ID, [Full Name], [Employee Type], [Employment Rate], [Manager ID]
FROM Employees
WHERE [Employment Rate] = 'Wage'

UNION ALL

SELECT 'Manager' as SOURCE,
       ID, [Full Name], [Employee Type], [Employment Rate], [Manager ID]
FROM Employees
WHERE ID IN (SELECT [Manager ID] FROM Employees Where [Employment Rate] = 'Wage')

Note -- if a employee has a wage rate they will appear twice with this query, but not with the first one.