SQL Server - Get all children of a row in many-to-many relationship?

Question

I'm trying to write a recursive query in SQL Server that basically lists a parent-child hierarchy from a given parent. A parent can have multiple children and a child can belong to multiple parents so it is stored in a many-to-many relation.

I modified the following query from another somewhat related question, however this doesn't go all the way up to the tree and only selects the first level child...

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

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

   UNION ALL

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

Does anyone know how to modify the query to go all the way down the 'tree'? Or is this not possible using the above construction?

Objects

Columns: ObjectId | Name

Objects_In_Objects

Columns: ObjectId | ParentObjectId

Sample data:

Objects

ObjectId                             | Name
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

Objects In Objects

ObjectId                             | ParentObjectId                    
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

Answer 1

Such a recursive CTE (Common Table Expression) will goo all the way .

Try this:

;WITH Tree AS
(
   SELECT A.ObjectID, A.ObjectName, o.ParentObjectID, 1 AS 'Level'
   FROM dbo.Objects A
   INNER JOIN dbo.Objects_In_Objects o ON A.ObjectID = o.ParentObjectID
   WHERE A.ObjectId = @ObjectId           -- use the A.ObjectId here

   UNION ALL

   SELECT A2.ObjectID, A2.ObjectName, B.ParentObjectID, t.Level + 1 AS 'Level'
   FROM Tree t 
   INNER JOIN dbo.Objects_In_Objects B ON B.ParentObjectID = t.ObjectID
   INNER JOIN dbo.Objects A2 ON A2.ObjectId = B.ObjectId        
)
SELECT *
FROM Tree
INNER JOIN dbo.Objects ar on tree.ObjectId = ar.ObjectId

If you change this - does this work for you now? (I added a Level column - typically helps to understand the "depth" in the hierarchy for every row)

I do seem to get the proper output on my SQL Server instance, at least...