Hierarchy example - Ken Henderson's book - not working

  • Like this? (All the same queries, but modified with Null. This leaves Groucho out altogether).

    create table staff(employee int primary key, employee_name varchar(10),

    supervisor int null references staff (employee))

    insert staff

    values

    (1, 'groucho', Null),

    (2, 'chico', 1),

    (3, 'harpo', 2),

    (4, 'zeppo', 2),

    (5, 'moe', 1),

    (6, 'larry', 5),

    (7, 'curly', 5),

    (8, 'shemp', 5),

    (9, 'joe', 8),

    (10, 'curly joe', 9);

    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

    insert into #org_chart

    select distinct o1.chartdepth+1, o2.employee, o1.supervisor

    from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)

    where o1.chartdepth=(select max(chartdepth) from #org_chart)

    and o1.supervisor is not null

    select s.employee_name, supervises='supervises', e.employee_name

    from #org_chart o join staff s on (o.supervisor=s.employee)

    inner join staff e on (o.employee=e.employee)

    where o.supervisor<>o.employee

    --Quote me

  • Ah...my apologies. I lost track of this thread. On my way to work. I'll try to post an example tonight.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Looking, thank you!

    --Quote me

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply