در صورت استفاده از SQL Server 2005 به بالا میتونید از روش زیر هم استفاده کنید
Create Table #ObjectLevel (
dcID int null,
dcParentID int null,
vcName nvarchar(50) null)
Create Table #Objects (
dcID int null,
dcGroupID int null,
vcModel nvarchar(50) null)
Insert Into #ObjectLevel (dcID, dcParentID, vcName) Values (1, 0, N'کامپیوتر')
Insert Into #ObjectLevel (dcID, dcParentID, vcName) Values (2, 1, N'کیبورد')
Insert Into #ObjectLevel (dcID, dcParentID, vcName) Values (3, 1, N'ماوس')
Insert Into #ObjectLevel (dcID, dcParentID, vcName) Values (4, 1, N'مانیتور')
Insert Into #ObjectLevel (dcID, dcParentID, vcName) Values (5, 4, N'LCD')
Insert Into #ObjectLevel (dcID, dcParentID, vcName) Values (6, 4, N'LED')
Insert Into #Objects (dcID, dcGroupID, vcModel) Values (1, 3, N'Genius')
Insert Into #Objects (dcID, dcGroupID, vcModel) Values (2, 3, N'Farassoo')
Insert Into #Objects (dcID, dcGroupID, vcModel) Values (3, 5, N'Samsung 1955')
Insert Into #Objects (dcID, dcGroupID, vcModel) Values (4, 6, N'Samsung 1965')
Select
O.dcID,
O.dcGroupID,
OL.vcName,
O.vcModel
From
#Objects O INNER JOIN #ObjectLevel OL
ON
O.dcGroupID = OL.dcID
Declare @LevelID int
Set @LevelID = 1 -- Computer Level
;With ChildIDWithSource (ID) as
(
Select @LevelID as ID
UNION ALL
Select O.dcID as ID
From
#ObjectLevel O INNER JOIN ChildIDWithSource CIWS
ON
O.dcParentID = CIWS.ID
)
Select *
From #Objects
Where dcGroupID IN (Select ID from ChildIDWithSource)
Drop Table #ObjectLevel
Drop Table #Objects
(از دستور With تا قبل از Drop کد مورد نظر شماست)