declare @Objects_In_Objects table ( ObjectID uniqueident...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

-- Enter Query Title
-- Enter Query Description

declare @Objects_In_Objects table
(
  ObjectID uniqueidentifier, 
  ParentObjectId uniqueidentifier
)

declare @Objects table
(
  ObjectId uniqueidentifier, 
  Name varchar(50)
)

insert into @Objects values
('1A213431-F83D-49E3-B5E2-42AA6EB419F1', 'Main container'),  
('63BD908B-54B7-4D62-BE13-B888277B7365', 'Sub container'),  
('71526E15-F713-4F03-B707-3F5529D6B25E', 'Sub container 2'),  
('ADA9A487-7256-46AD-8574-0CE9475315E4', 'Object in multiple containers')

insert into @Objects_In_Objects values
('ADA9A487-7256-46AD-8574-0CE9475315E4', '71526E15-F713-4F03-B707-3F5529D6B25E'),
('ADA9A487-7256-46AD-8574-0CE9475315E4', '63BD908B-54B7-4D62-BE13-B888277B7365'),
('63BD908B-54B7-4D62-BE13-B888277B7365', '1A213431-F83D-49E3-B5E2-42AA6EB419F1'),
('71526E15-F713-4F03-B707-3F5529D6B25E', '1A213431-F83D-49E3-B5E2-42AA6EB419F1')


DECLARE @ObjectId uniqueidentifier
SET @ObjectId = '1A213431-F83D-49E3-B5E2-42AA6EB419F1';

WITH Tree AS
(
   SELECT A.ObjectID,
          A.ParentObjectId
   FROM @Objects_In_Objects A
   WHERE A.ParentObjectId = @ObjectId

   UNION ALL

   SELECT B.ObjectID,
          B.ParentObjectId
   FROM Tree A
   JOIN @Objects_In_Objects B
   ON B.ParentObjectId = A.ObjectId
)
SELECT *
FROM Tree
INNER JOIN @Objects ar on tree.ObjectId = ar.ObjectId;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached