SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DepTable](
    [ID] [int] NOT NULL,
    [DepName] [nvarchar](50) NULL,
    [ParentID] [int] NULL,
CONSTRAINT [PK_DepTable] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DepData](
    [DepDataID] [int] NOT NULL,
    [DepID] [int] NULL,
    [info1] [date] NULL,
    [info2] [nvarchar](50) NULL,
    [info3] [money] NULL,
CONSTRAINT [PK_DepData] PRIMARY KEY CLUSTERED
(
    [DepDataID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO



ALTER TABLE [dbo].[DepData]  WITH CHECK ADD  CONSTRAINT [FK_DepData_DepTable] FOREIGN KEY([DepID])
REFERENCES [dbo].[DepTable] ([ID])
GO

ALTER TABLE [dbo].[DepData] CHECK CONSTRAINT [FK_DepData_DepTable]
GO

ALTER TABLE [dbo].[DepTable]  WITH CHECK ADD  CONSTRAINT [FK_DepTable_DepTable] FOREIGN KEY([ParentID])
REFERENCES [dbo].[DepTable] ([ID])
GO

ALTER TABLE [dbo].[DepTable] CHECK CONSTRAINT [FK_DepTable_DepTable]
GO

--SQL Server 2008
INSERT
INTO            DepTable(ID, DepName, ParentID)
VALUES     

(1,    N'ადმინისტრაცია',NULL),
(2,    N'ბუღალტერია',1),
(3,    N'კადრები',1),
(4,    N'ლაბორატორია',1),
(5,    N'მექანიკური განყოფილება',1),
(6,    N'შვილი განყოფილება 1',    5),
(7,    N'შვილი განყოფილება 2',    5),
(8,    N'სამშენებლო განყოფილება',1),
(9,    N'შვილი განყოფილება 1',    8),
(10,N'შვილი განყოფილება 2',    8)

-- SQL Server 2005, 2008
DECLARE @parID int;
SET @parID = 1;
WITH cte AS
(
SELECT     ID, DepName, ParentID, 0 AS nlevel
FROM         DepTable
WHERE     (ID = @parID)
UNION ALL
SELECT     a.ID, a.DepName, a.ParentID, b.nlevel + 1 AS nlevel
FROM         DepTable AS a INNER JOIN
                      cte AS b ON b.ID = a.ParentID
)
SELECT ID, DepName, ParentID
FROM cte

-- SQL Server 2005, 2008
DECLARE @parID int;
SET @parID = 8;
WITH cte AS
(
SELECT     ID, DepName, ParentID, 0 AS nlevel
FROM         DepTable
WHERE     (ID = @parID)
UNION ALL
SELECT     a.ID, a.DepName, a.ParentID, b.nlevel + 1 AS nlevel
FROM         DepTable AS a INNER JOIN
                      cte AS b ON b.ID = a.ParentID
)
SELECT ID, DepName, ParentID
FROM cte

DECLARE @parID int;
SET @parID = 8;
WITH cte AS
(
SELECT     ID, DepName, ParentID, 0 AS nlevel
FROM         DepTable
WHERE     (ID = @parID)
UNION ALL
SELECT     a.ID, a.DepName, a.ParentID, b.nlevel + 1 AS nlevel
FROM         DepTable AS a INNER JOIN
                      cte AS b ON b.ID = a.ParentID
)
SELECT     d.DepDataID, d.DepID, d.info1, d.info2, d.info3
FROM         DepData AS d INNER JOIN
                      DepTable AS t ON d.DepID = t.ID