Yup, the recursive CTE gave me what was needed. I needed to be able to get one particular Top Dude's descendents (employees), and this worked ok:
USE Sandbox;
GO
-- 1 and 12 are the top dudes, and we want to be able to get each's tree separately
DECLARE @TopDude BIGINT;
SET @TopDude = 12;
WITH DirectReports (boss_id, employee_id, [level])
AS
(
-- Anchor member definition
SELECT e.boss_id, e.employee_id,
0 AS [level]
FROM dbo.Employee_Hierarchy AS e
--WHERE boss_id IS NULL
WHERE boss_id = 0
and employee_id = @TopDude
UNION ALL
-- Recursive member definition
SELECT e.boss_id, e.employee_id,
[level] + 1
FROM dbo.Employee_Hierarchy AS e
INNER JOIN DirectReports AS d
ON e.boss_id = d.employee_id
)
-- Statement that executes the CTE
SELECT R.boss_id
, B.employee_first_name + ' ' + B.employee_last_name + ', ' + B.employee_title as boss
, R.employee_id
, E.employee_first_name + ' ' + E.employee_last_name + ', ' + E.employee_title as employee
, [level]
FROM DirectReports R
INNER JOIN dbo.Employee E
ON R.employee_id = E.employee_id
LEFT JOIN Employee B
ON R.boss_id = B.employee_id
and here's the DDL for Employee_Hierarchy:
USE [Sandbox]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee_Hierarchy](
[employee_hierarchy_id] [int] IDENTITY(1,1) NOT NULL,
[employee_id] [int] NOT NULL,
[boss_id] [int] NULL,
CONSTRAINT [PK_Employee_Hierarchy] PRIMARY KEY NONCLUSTERED
(
[employee_hierarchy_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee_Hierarchy] WITH CHECK ADD CONSTRAINT [FK_Employee_Hierarchy_Employee] FOREIGN KEY([boss_id])
REFERENCES [dbo].[Employee] ([employee_id])
GO
ALTER TABLE [dbo].[Employee_Hierarchy] CHECK CONSTRAINT [FK_Employee_Hierarchy_Employee]
GO
ALTER TABLE [dbo].[Employee_Hierarchy] WITH CHECK ADD CONSTRAINT [FK_Employee_Hierarchy_Employee_Hierarchy] FOREIGN KEY([employee_hierarchy_id])
REFERENCES [dbo].[Employee_Hierarchy] ([employee_hierarchy_id])
GO
ALTER TABLE [dbo].[Employee_Hierarchy] CHECK CONSTRAINT [FK_Employee_Hierarchy_Employee_Hierarchy]
GO
ALTER TABLE [dbo].[Employee_Hierarchy] WITH CHECK ADD CONSTRAINT [FK_Employee_Hierarchy_Employee2] FOREIGN KEY([employee_id])
REFERENCES [dbo].[Employee] ([employee_id])
GO
ALTER TABLE [dbo].[Employee_Hierarchy] CHECK CONSTRAINT [FK_Employee_Hierarchy_Employee2]
GO
CREATE UNIQUE CLUSTERED INDEX [UIX_Employee_Hierarchy] ON [dbo].[Employee_Hierarchy]
(
[employee_id] ASC,
[boss_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO