For those of you who followed this thread, did anyone use this method to handle foreign keys with circular references?
I adapted a couple of the posted examples, and against one of my databases which i know has circular references, I'm thinking this is not really going to give results in a hierarchy correctly; it seems that the hierarchy is more of the count of FK levels between the objects, and not necessarily the hiarchy order.
i was fiddling around with this trying to get all objects in a hierarchy, but
It seems to me to be pretty inefficient; 20 plus minutes on a machine with no resources in use at all, along with 60,000 rows;
parent_object_id AS object_id,
convert(varchar(255),OBJECT_NAME(parent_object_id)) AS child,
referenced_object_id AS referenced_major_id,
convert(varchar(255),OBJECT_NAME(referenced_object_id)) AS parent
,GroupMembers (Child, ParentGroup, Level, hierarchypath)
(-- Anchor member definition
0 AS Level,
convert(varchar(max), g.child + '->' + g.parent) AS hierarchypath
FROM ChildrenAndParents AS g
WHERE child not in (select parent from ChildrenAndParents)
-- Recursive member definition
Level + 1,
hierarchypath + '->' + g.parent -- Add '-->...' to end when recursion found
+ Case When gm.hierarchypath like '%->'+g.parent+'->%' Then '-->...'
FROM ChildrenAndParents as g
INNER JOIN GroupMembers AS gm
ON gm.parentgroup = g.child
--Exclude if the hierarchypath text contains a recursion
where gm.hierarchypath not like '%->...'
select left(hierarchypath, charindex('->', hierarchypath) - 1) as child, parentgroup, level, hierarchypath
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!