Expanding a tree?

Expanding a tree?

Post by KS » Sat, 04 Aug 2001 05:05:34



I ran into a problem for which I cannot find a solution that satisfies me.  I
wonder if someone would have something clever on this.

I have a traditional organizational tree table: EmployeeID, ManagerID, self
referencing.

What I need is to get from it a table of the structure ManagerID, EmployeeID,
where there is an entry for every direct OR indirect replationship between each
manager and each employee.  So it A manages B and Y, B manages C and X, and C
manages D:

DirectManagerID, EmployeeID
A,B
A,Y
B,C
B,X
C,D

or diagramatically:

A
|
+--> B  Y
|
+--> C  X
|
+--> D

then I want to produce a table:

ManagerID, EmployeeID
A,B
A,C
A,D
A,X
A,Y
B,C
B,X
B,D
C,D

I cannot predict the depth of the command chain, but I assume it to be something
like 15-16 at most (above diagram shows 4 levels).

Any clever solutions?

Problem B:

A more complicated variant is that the EmployeeID may be NON-unique -- an
employee may have more than one manager if s/he works on more than one project.

DirectManagerID, EmployeeID
A,B
A,Y
B,C
B,X
C,D
M,C

A
|
+-->  B  Y           M
|              |
+--> X  C <----+ [M co-manages C only, M does NOT manage X]
|
+--> D

ManagerID, EmployeeID
A,B
A,C
A,D
A,X
A,Y
B,C
B,X
B,D
C,D
M,C
M,D

Any clever ideas on this one?

Thanks

I got tired of spam, so my email address is protected.  If you really need to
know what it is, please utilize http://www.rtg.se/~niclas/rot13 or
http://www.rtg.se/~niclas/reddwarf/rot13, either followed by a period and an
"htm".

 
 
 

Expanding a tree?

Post by Darren Brinksneade » Sat, 04 Aug 2001 05:11:04


Give this a try...

USE NORTHWIND
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'pFindHierarchy' AND type =
'P')
 DROP PROCEDURE pFindHierarchy
GO


AS
SET NOCOUNT ON


CREATE TABLE #TempTable (EmployeeID INT, ReportsTo INT, nLevel INT,
Hierarchy VARCHAR(255))


 BEGIN


   BEGIN
    PRINT 'You are at top of hierarchy'
    RETURN
   END
  ELSE
   BEGIN

    WHILE EXISTS (SELECT *
                FROM #TempTable t INNER JOIN employees e
                  ON t.ReportsTo = e.EmployeeID
                WHERE t.ReportsTo NOT IN (SELECT EmployeeID FROM
#TempTable))
     BEGIN

      INSERT INTO #TempTable

         FROM #TempTable t INNER JOIN Employees e
           ON t.ReportsTo = e.EmployeeID
        WHERE t.ReportsTo NOT IN (SELECT EmployeeID FROM #TempTable)
     END



    UPDATE #TempTable


     BEGIN
      UPDATE t1
       SET Hierarchy = t2.Hierarchy + '.' +  t1.Hierarchy
       FROM #TempTable t1 INNER JOIN #TempTable t2 ON t2.EmployeeID =
t1.ReportsTo


     END
  END
 END
ELSE

 BEGIN


  IF NOT EXISTS(SELECT *
                FROM #TempTable t INNER JOIN employees e
                  ON t.EmployeeID = e.ReportsTo
                WHERE e.EmployeeID NOT IN (SELECT EmployeeID FROM
#TempTable))
   BEGIN
    PRINT 'There are no children'
    RETURN
   END
  WHILE EXISTS (SELECT *
                FROM #TempTable t INNER JOIN employees e
                  ON t.EmployeeID = e.ReportsTo
                WHERE e.EmployeeID NOT IN (SELECT EmployeeID FROM
#TempTable))
   BEGIN

    INSERT INTO #TempTable

CAST(e.EmployeeID as VARCHAR(3))
        FROM #TempTable t INNER JOIN Employees e
          ON t.EmployeeID = e.ReportsTo
       WHERE e.EmployeeID NOT IN (SELECT EmployeeID FROM #TempTable)
   END
 END

--SELECT TOP 1 COUNT(*) as LevelCount , nlevel FROM #TempTable GROUP BY
nLevel ORDER BY 1 DESC


 SELECT REPLICATE(' . ',nLevel) + e.LastName as Employee
   FROM Employees e INNER JOIN #TempTable t ON e.EmployeeID = t.EmployeeID
  ORDER BY Hierarchy
ELSE
  SELECT * FROM #TempTable ORDER BY Hierarchy
GO

pFindHierarchy 2

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT


Quote:> I ran into a problem for which I cannot find a solution that satisfies me.
I
> wonder if someone would have something clever on this.

> I have a traditional organizational tree table: EmployeeID, ManagerID,
self
> referencing.

> What I need is to get from it a table of the structure ManagerID,
EmployeeID,
> where there is an entry for every direct OR indirect replationship between
each
> manager and each employee.  So it A manages B and Y, B manages C and X,
and C
> manages D:

> DirectManagerID, EmployeeID
> A,B
> A,Y
> B,C
> B,X
> C,D

> or diagramatically:

> A
> |
> +--> B  Y
> |
> +--> C  X
> |
> +--> D

> then I want to produce a table:

> ManagerID, EmployeeID
> A,B
> A,C
> A,D
> A,X
> A,Y
> B,C
> B,X
> B,D
> C,D

> I cannot predict the depth of the command chain, but I assume it to be
something
> like 15-16 at most (above diagram shows 4 levels).

> Any clever solutions?

> Problem B:

> A more complicated variant is that the EmployeeID may be NON-unique -- an
> employee may have more than one manager if s/he works on more than one
project.

> DirectManagerID, EmployeeID
> A,B
> A,Y
> B,C
> B,X
> C,D
> M,C

> A
> |
> +-->  B  Y           M
> |              |
> +--> X  C <----+ [M co-manages C only, M does NOT manage X]
> |
> +--> D

> ManagerID, EmployeeID
> A,B
> A,C
> A,D
> A,X
> A,Y
> B,C
> B,X
> B,D
> C,D
> M,C
> M,D

> Any clever ideas on this one?

> Thanks

> I got tired of spam, so my email address is protected.  If you really need
to
> know what it is, please utilize http://www.rtg.se/~niclas/rot13 or
> http://www.rtg.se/~niclas/reddwarf/rot13, either followed by a period and
an
> "htm".


 
 
 

Expanding a tree?

Post by Joe Celk » Sat, 04 Aug 2001 08:56:31


Look up the Nested set model for heirarchies in the archives.  You can do what you want in one simple query.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. SEM 2000 extremely slow to expand database tree

Has anyone else found that trying to expand the Databases tree using SEM
2000 takes much longer than in 7.0?  I ran a trace of what's going on, and
found that the following query was being run once for each database on the
server, despite the fact that the query returns status information for all
the databases.  On my laptop this query takes around 2 seconds to run, which
for my 12 databases means a wait of about 30 seconds.  On our test server
with 126 databases, the query runs in 600 ms, resulting in over a minute's
wait just to list the databases.

Worse, some operations in SEM (in particular, restoring a database) cause
the whole process to be repeated!  So going into Entrprise Manager to
restore a database (something I do several times a day) might take three
minutes, before I can even start the restore.

select name, DATABASEPROPERTY(name, N'IsDetached'),     (case when
DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else
DATABASEPROPERTY(name, N'IsShutdown') end),     DATABASEPROPERTY(name,
N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'),
DATABASEPROPERTY(name, N'IsInLoad'),     (case when DATABASEPROPERTY(name,
N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name,
N'IsInRecovery') end),     (case when DATABASEPROPERTY(name,
N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name,
N'IsNotRecovered') end),     DATABASEPROPERTY(name, N'IsEmergencyMode'),
DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name),     status,
category, status2 from master.dbo.sysdatabases

2. Disconnect Or Not ???

3. Where do I put rlock() procedures in screens?

4. Database Tree expands very slowly in Enterprise Manager (after registry rebuild)

5. Informix Pricing

6. Databases, Data Structures, B Trees, B+ Trees, Patricia Trees

7. contrib/tree/README.tree

8. R-tree or B-tree

9. T-tree or T*-tree source code?

10. B+tree or B-tree