Hello,

department-table definition:

dept varchar(6)
mother_dept varchar(6)
manager
etc

I want to make a query that gives me all the depts under a given dept. (all
levels, not only the first)

Thanks in advance

Jan Aerts

Maybe this will help:

SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Employees') IS NOT NULL
  DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL PRIMARY KEY,
  mgrid   INT         NULL     REFERENCES dbo.Employees,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL
);

INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(1, NULL, 'David', $10000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(2, 1, 'Eitan', $7000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(3, 1, 'Ina', $7500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(4, 2, 'Seraph', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(5, 2, 'Jiru', $5500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(6, 2, 'Steve', $4500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(7, 3, 'Aaron', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(8, 5, 'Lilach', $3500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(9, 7, 'Rita', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(10, 5, 'Sean', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(11, 7, 'Gabriel', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(12, 9, 'Emilia' , $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(13, 9, 'Michael', $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(14, 9, 'Didi', $1500.00);

CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);
GO

USE tempdb;
GO
IF OBJECT_ID('dbo.fn_subordinates1') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates1;
GO
CREATE FUNCTION dbo.fn_subordinates1(@root AS INT) RETURNS @Subs Table
(
  empid INT NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT NOT NULL,
  UNIQUE CLUSTERED(lvl, empid)  -- Index will be used to filter level
)
AS
BEGIN
  DECLARE @lvl AS INT;
  SET @lvl = 0;                 -- Init level counter with 0

  -- Insert root node to @Subtree
  INSERT INTO @Subs(empid, lvl)
    SELECT empid, @lvl FROM dbo.Employees WHERE empid = @root;

  WHILE @@rowcount > 0          -- while prev level had rows
  BEGIN
    SET @lvl = @lvl + 1;        -- Increment level counter

    -- Insert next level of subordinates to @Subtree
    INSERT INTO @Subs(empid, lvl)
      SELECT C.empid, @lvl
      FROM @Subs AS P           -- P = Parent
        JOIN dbo.Employees AS C -- C = Child
          ON P.lvl = @lvl - 1   -- Filter parents from prev level
          AND C.mgrid = P.empid;
  END

  RETURN;
END
GO

This solution is iterative and does not require you to materialize
additional information to the database. There are other solutions that
materialize additional info and allow simple and fast set-based queries.
e.g., Materialized Path:

http://www.windowsitpro.com/Article/ArticleID/8826/8826.html

Also Nested Sets which you will find by searching this newsgroup or googling
Nested Sets, Joe Celko.

If you're looking for recursive CTE solutions in SQL Server 2005, check out:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp

Show quoteHide quote

A few options to investigate:

-Look up recursive CTE for SQL Server 2005 (if you are using that version)
-Loop up "Expanding Hierarchies" in BOL
-Search for Nested Sets, Nested Intervals, or Enumerated Paths in this
newsgroup

You might want to first look at separating the department relationship from
the department definitions (two tables - one to define each department, and
one to describe the relationship between departments)

Show quoteHide quote

Get a copy of TREES & HIERARCHIES IN SQL and look up the nested setts
model for trees.

IDENTITY_INSERT
Conditional select into on temp table
Inserting data to a table from other database. Help
Removing Date/Time Stamp from Add Remove Data in SMS table
ISNUMERIC