Family parent child relation-CodePudding

I need the following hierarchy showing in the following image

I have write the following code with cte : --=========================

;With Hierarchy (Name,Path)
AS
(
SELECT t.FamilyName, CAST(t.FamilyName AS varchar(max))
FROM tblFamily t
LEFT JOIN tblFamily t1
ON t1.FamilyID = t.ParentFamilyId
WHERE t1.FamilyID IS NULL
UNION ALL
SELECT t.FamilyName, CAST('All Categories' '>'  h.FamilyName   '->'   t.FamilyName AS varchar(max))
FROM tblFamily h
INNER JOIN tblFamily t
ON t.FamilyID = h.ParentFamilyId
)



SELECT Name,Path
FROM Hierarchy
OPTION (MAXRECURSION 0)

i have hardcoded the All categories which is the parent to all the child but i also need this natural how to do this? Here is the table : enter image description here

CodePudding user response：

You are creating the tree in the wrong direction. It's much easier in this case to start at the top of the tree and work down.

WITH Hierarchy
AS
(
    SELECT t.FamilyID, t.FamilyName, FamilyPath = CAST(t.FamilyName AS varchar(max))
    FROM tblFamily t
    WHERE t.ParentFamilyId IS NULL

    UNION ALL

    SELECT t.FamilyID, t.FamilyName, h.FamilyPath   ' > '   t.FamilyName
    FROM tblFamily t
    INNER JOIN Hierarchy h ON h.FamilyID = t.ParentFamilyId
)
SELECT
  FamilyID,
  FamilyName,
  FamilyPath
FROM Hierarchy;

Note that using MAXRECURSION 0 is usually a bad idea, because most trees don't go anywhere near that far, and this could cause an infinite recursion if there is a loop in the hierarchy.

CodePudding user response：

Your assignment tells you to user outer joins to link three references to the same table, and even tells you what to alias the tables. There's nothing about CTEs or recursion.

SELECT
  Family.*,
  COALESCE(TopFamily.FamilyName   ' > ', '')
    COALESCE(ParentFamily.FamilyName   ' > ', '')
    Family.FamilyName
    AS FamilyPath
FROM
  tblFamily   AS Famiky
LEFT JOIN
  tblFamily   AS ParentFamily
    ON ParentFamily.FamilyID = Family.ParentFamilyID
LEFT JOIN
  tblFamily   AS TopFamily
    ON TopFamily.FamilyID = ParentFamily.ParentFamilyID

The first reference gets all family entries, regardless of level in the hierarchy.

The left join gets the parent, if there is one, and NULLs if there isn't one. The second left join does the same again, essentially getting the Grandparent, if there is one.

This means that it only works for a set depth, but that's what your assignment asked for.

The SELECT clause wraps each level's FamilyName in a COALESCE(), so that the > is only added to the name if it isn't NULL, and replaces any NULL with an empty string (joining NULL to another string just gives NULL).