Hi!

I just bought the book "SQL for smarties" by Joe Celko. Here he shows
the possibility to use the nested set model. I am in the situation where
I need to model a hierarci: One company has several regions that has
several districts that has several projects that has several buildings
that has several stories, rooms, etc. The key issue is that the possible
levels are unknown. To the different levels one can attach different
documents from the database. I have been able to make a table that uses
the nested set model and query it. What I am wondering now is if any one
has done some programming in asp or other programming language to get
the right output.

Here is the query:
select count(p2.nivId) as indentation, p1.pnavn, p1.lft, p1.rgt
from organisasjon p1, organisasjon p2
where p1.lft between p2.lft and p2.rgt
and p1.levid = 1000003 and p2.levid = 1000003
group by p1.pnavn, p1.lft, p1.rgt
order by indentation, p1.lft

Here is a result from a query:

ind.    name of level           lft     rgt
1    Skanska Norway            1    18
2    Bøhmer Construction    2    3
2    Buer Construction    4    17
3    Project1            5    14
3    Region West            15    16
4    District Bergen            6    13
5    Project2            7    12
6    Carpenter            8    11
7    Plumber                    9    10

Another question is the use of the new reqursive queries in SQL Server
2005. Would this make things easier in any way?

Thanks!

Henning :-)

*** Sent via Developersdex http://www.developersdex.com ***

I'm not sure I understand what exactly you're after in your first question;
are you looking for a way to graphically create the indentation?
Either in T-SQL or in the client, simply replicate a string indentation
times:

SELECT REPLICATE(' | ', indentation) + pnavn
FROM (your query) AS D
ORDER BY lft

BTW, I find it much more efficient to actually store the level in the tree
as an additional column. Makes the queries that need indentation, or level
diff calculations so much more simple and efficient.

As for your second question about recursive CTEs in SQL Server 2005, yes,
there are many things that will be easier.
For details, please refer to:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp
Still, there might be justification to materializing a path enumeration, or
nested sets left/right values in some cases.

If your question is specific to calculating left and right values, Recursive
CTE will make it much simpler and more efficient to calculate them.
I have to run now so I don't have time to explain the solution, but here's
code that will allow you to create those values:

-- DDL & Sample Data for Employees
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

-- Code to generate level, left, right values for all subtree of a given
root
DECLARE @root AS INT;
SET @root = 1;

-- CTE with two numbers: 1 and 2
WITH TwoNumsCTE
AS
(
  SELECT 1 AS n UNION ALL SELECT 2
),
-- CTE with two binary sort paths for each node:
--   One smaller than descendants sort paths
--   One greater than descendants sort paths
SortPathCTE
AS
(
  SELECT empid, 0 AS lvl, n,
    CAST(n AS VARBINARY(MAX)) AS sortpath
  FROM dbo.Employees CROSS JOIN TwoNumsCTE
  WHERE empid = @root

  UNION ALL

  SELECT C.empid, P.lvl + 1, TN.n,
    P.sortpath + CAST(
      ROW_NUMBER() OVER(PARTITION BY C.mgrid ORDER BY C.empname, TN.n)
      AS BINARY(4))
  FROM SortPathCTE AS P
    JOIN dbo.Employees AS C
      ON P.n = 1
      AND C.mgrid = P.empid
    CROSS JOIN TwoNumsCTE AS TN
),
-- CTE with Row Numbers Representing sortpath Order
SortCTE
AS
(
  SELECT empid, lvl,
    ROW_NUMBER() OVER(ORDER BY sortpath) AS sortval
  FROM SortPathCTE
),
-- CTE with Left and Right Values Representing
-- Nested Sets Relationships
NestedSetsCTE
AS
(
  SELECT empid, lvl, MIN(sortval) AS lft, MAX(sortval) AS rgt
  FROM SortCTE
  GROUP BY empid, lvl
)
SELECT * FROM NestedSetsCTE;

/*
empid       lvl         lft                  rgt
----------- ----------- -------------------- --------------------
1           0           1                    28
2           1           2                    13
5           2           3                    8
8           3           4                    5
10          3           6                    7
4           2           9                    10
6           2           11                   12
3           1           14                   27
7           2           15                   26
11          3           16                   17
9           3           18                   25
14          4           19                   20
12          4           21                   22
13          4           23                   24
*/

Cheers,
Show quoteHide quote

3 Questions on db Theory/Design
restoring db without log
Upgrade from SQL Server 7.0 to SQL Server 2000
How to get the count of rows selected in a cursor
Best Practice to Simulate Time