I have two tables in my database:
Offices
id Department Section PositionID ------------------------------------------------------- 1 | DataCenter | Developer | 10 2 | DataCenter | DBA | 11 3 | DataCenter | SA | 12 4 | DataCenter | HelpDesk | 13 5 | DataCenter | DepHead | 14 6 | Produce | Stocker | 19 7 | Produce | Farmer | 20
Positions
id PosName OfficeID Level ------------------------------------------------------- 10 | Senior Dev | 1 | 1 11 | Senior DBA | 2 | 1 12 | Senior SA | 3 | 1 13 | Help Desk | 4 | 2 14 | Supervisor | 1 | 0 15 | Junior Dev | 1 | 2 16 | Junior Dev | 3 | 2 17 | Junior DBA | 2 | 2 18 | Junior DBA | 2 | 2 19 | Junior DBA | 2 | 2 20 | Junior SA | 1 | 2
How can I select all entries from Positions based on their Department and Section, but one above their level? I’m guessing I need a join with some kind of condition that gets evaluated to do this.
For example, the Senior Dev position is at level 1. So, I would like for the two Junior Dev positions at level 2 to grouped with it. By the same token, the three level 2 Junior DBA positions would be associated with the Senior DBA position. And the trickiest part would be to get ALL the positions in the DataCenter associated with the Supervisor (who is at level 0).
My end goal is to use this query in a web page where users assigned to a position can only see users who work under them and in their section/department.
So perhaps the end query result might look something like this:
Department Section ManagingPosition Position PositionID ----------------------------------------------------------------------------------- DataCenter | Developer | Senior Dev | Junior Dev | 15 DataCenter | Developer | Senior Dev | Junior Dev | 16 DataCenter | DBA | Senior DBA | Junior DBA | 17 DataCenter | DBA | Senior DBA | Junior DBA | 18 DataCenter | DBA | Senior DBA | Junior DBA | 19 DataCenter | SA | Senior SA | Junior SA | 20 DataCenter | DepHead | Supervisor | Junior Dev | 15 DataCenter | DepHead | Supervisor | Junior Dev | 16 DataCenter | DepHead | Supervisor | Junior DBA | 17 DataCenter | DepHead | Supervisor | Junior DBA | 18 DataCenter | DepHead | Supervisor | Junior DBA | 19 DataCenter | DepHead | Supervisor | Junior SA | 20 DataCenter | DepHead | Supervisor | Help Desk | 13
So far, I have
SELECT
O.Department,
O.Section,
O.Position AS ManagingPosition,
P.PosName AS Position,
P.PositionID
FROM
Offices O
INNER JOIN Positions P on O.PositionID = P.id
I know this is incorrect, though. What’s the best way to query these tables to get the results I want? I can also modify these tables (add columns) to get results as well. I appreciate any help!
Edit:
For clarification, the Levels in the Positions table start with level 0 being the highest, and level 2 being the lowest. So level 0 should be able to see anything greater (i.e. level 1 and 2), and level 1 should only be able to see level 2.
Advertisement
Answer
First you need to add a column to define the parent position for each position.
And then an option is recursion with CTE (https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/)
WITH PositionsCTE ( PositionID, ManagingPosition, Position, OfficeId, [Level])
AS
(
--starts with the very first level
SELECT
id as PositionID, cast('' as varchar(50)) as ManagingPosition,
PosName, OfficeId, [Level]
FROM Positions
WHERE ParentId IS NULL -- (supervisor in this case)
UNION ALL
-- and then recursivity
SELECT
p.id as PositionID, Pcte.Position as ManagingPosition,
p.PosName, p.OfficeId, p.[Level]
FROM Positions AS p
INNER JOIN PositionsCTE Pcte ON p.ParentId = Pcte.PositionID
)
Select
o.Department,
o.Section,
p.ManagingPosition,
p.Position,
p.PositionID
from Offices o
inner join PositionsCTE p on o.id = p.OfficeId;