Sql Server - Get All Children Of A Row In Many-to-many Relationship?

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

Solution 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, 1AS'Level'FROM dbo.Objects A
   INNERJOIN dbo.Objects_In_Objects o ON A.ObjectID = o.ParentObjectID
   WHERE A.ObjectId =@ObjectId-- use the A.ObjectId hereUNIONALLSELECT A2.ObjectID, A2.ObjectName, B.ParentObjectID, t.Level +1AS'Level'FROM Tree t 
   INNERJOIN dbo.Objects_In_Objects B ON B.ParentObjectID = t.ObjectID
   INNERJOIN dbo.Objects A2 ON A2.ObjectId = B.ObjectId        
)
SELECT*FROM Tree
INNERJOIN 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...

Solution 2:

declare@Objects_In_Objects table
(
  ObjectID uniqueidentifier, 
  ParentObjectId uniqueidentifier
)

declare@Objectstable
(
  ObjectId uniqueidentifier, 
  Name varchar(50)
)

insertinto@Objectsvalues
('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')

insertinto@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 =@ObjectIdUNIONALLSELECT B.ObjectID,
          B.ParentObjectId
   FROM Tree A
   JOIN@Objects_In_Objects B
   ON B.ParentObjectId = A.ObjectId
)
SELECT*FROM Tree
INNERJOIN@Objects ar on tree.ObjectId = ar.ObjectId;

Is this what you are looking for? https://data.stackexchange.com/stackoverflow/q/111357/

Solution 3:

Can this help you ?

http://www.aghausman.net/sql_server/storingretrieving-hierarchical-data-in-sql-server-database.html