Ok. I'm totally confused as to what Ken was trying to demonstrate. Let's take his first query from your last post.

select chartdepth=1, employee=o2.employee, supervisor=o1.employee

into #org_chart

from staff o1 inner join staff o2 on (o1.employee=o2.supervisor)

where o1.supervisor is not null

The result of that query is the same as the following and requires no join at all because all that it is ultimately doing is returning the contents of the original table with an extra column added.

SELECT ChartDepth = 1,

Employee,

Supervisor

FROM dbo.Staff

;

The second "paragraph" of code is clever but it produces a very nasty Cartesian Product (full Cross Join). I don't know if that would resolve itself out to a simple hash join when the table get's larger but, if it doesn't, this code would take a millenia to run on what some folks consider to be a "small" million node hierarchy that frequents the world of MLMs and some parts lists.

The third "paragraph" of code is, again, clever, but I'm not sure that you'd ever want to display something like that for a larger hierarchy and so I don't understand the overall reason for demonstrating this method.

Getting back to (literally) the root of what I was talking about (Groucho needs to have a NULL for supervisor), the original code isn't easily modified to allow for such a thing. Again, although it's clever code, I'm not sure why anyone would want to display the output of this code.

I guess the question at this point is, what would you actually like to do with this hierarchy example? Something like this? Although I don't particularly like the column names or the table name, I kept them for comparison.

CREATE TABLE dbo.Staff

(

Employee INT NOT NULL,

Employee_Name VARCHAR(10) NOT NULL,

Supervisor INT NULL

CONSTRAINT PK_Staff

PRIMARY KEY CLUSTERED (Employee ASC),

CONSTRAINT FK_Staff_ManagerMustBeAnEmployee

FOREIGN KEY (Supervisor)

REFERENCES dbo.Staff (Employee),

CONSTRAINT CK_Staff_ManagerCannotBeSelf

CHECK (Supervisor<>Employee)

)

;

INSERT INTO dbo.Staff

(Employee,Employee_Name,Supervisor)

SELECT 1, 'Groucho' , NULL UNION ALL --1 UNION ALL

SELECT 2, 'Chico' , 1 UNION ALL

SELECT 3, 'Harpo' , 2 UNION ALL

SELECT 4, 'Zeppo' , 2 UNION ALL

SELECT 5, 'Moe' , 1 UNION ALL

SELECT 6, 'Larry' , 5 UNION ALL

SELECT 7, 'Curly' , 5 UNION ALL

SELECT 8, 'Shemp' , 5 UNION ALL

SELECT 9, 'Joe' , 8 UNION ALL

SELECT 10, 'Curly Joe' , 9

;

WITH

cteDirectReports AS

( --=== This gets the top-level supervisors(s).

-- This would be the "primer" for a loop.

SELECT Employee, Supervisor, Employee_Name, EmployeeLevel = 1,

HierarchicalPath = CAST('\'+CAST(Employee AS VARCHAR(10)) AS VARCHAR(4000))

FROM dbo.Staff

WHERE Supervisor IS NULL

UNION ALL

--==== This gets a level at a time using recursion of the CTE.

-- This would be the "body" of a loop.

SELECT e.Employee, e.Supervisor, e.Employee_Name, EmployeeLevel = d.EmployeeLevel + 1,

HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.Employee AS VARCHAR(10)) AS VARCHAR(4000))

FROM dbo.Staff e

INNER JOIN cteDirectReports d ON e.Supervisor = d.Employee

) --=== This simply formats for display. I wouldn't do this in real life, either.

SELECT Employee,

Supervisor,

Employee_Name = SPACE((EmployeeLevel-1)*4) + Employee_Name,

EmployeeLevel,

HierarchicalPath

FROM cteDirectReports

ORDER BY HierarchicalPath

;

That produces the following indented name result...

Employee SupervisorEmployee_Name EmployeeLevel HierarchicalPath

1 NULL Groucho 1 \1

2 1 Chico 2 \1\2

3 2 Harpo 3 \1\2\3

4 2 Zeppo 3 \1\2\4

5 1 Moe 2 \1\5

6 5 Larry 3 \1\5\6

7 5 Curly 3 \1\5\7

8 5 Shemp 3 \1\5\8

9 8 Joe 4 \1\5\8\9

10 9 Curly Joe 5 \1\5\8\9\10

The "Hierarchical Path" contains the chain of command from the top level all the way down to the individual employee. If done in a Binary(4) format, you can easily use it to do some really remarkable things. It's kind of like the "new" HierarchyID datatype but better, IMHO.

For a much more detailed explanation of how that works, please see the following article.

http://www.sqlservercentral.com/articles/T-SQL/72503/