MSSQL recursively selects all related rows

I have a definition / field relationship hierarchy that spans 3 tables:

CREATE TABLE ProductDefinition
(
  ProductDefinitionId int,
  Name nvarchar(10),
)

CREATE TABLE ProductDefinitionRelation
(
  ProductDefinitionId int,
  ParentProductDefinitionId int
)

CREATE TABLE ProductDefinitionField
(
  ProductDefinitionFieldId int,
  ProductDefinitionId int,
  Name nvarchar(10)
)

      

        
        
        
      

    

The idea is that this allows the CMS editor to share common properties between objects, saving the admin administration time.

The structure is from a third party, so it cannot be changed, but now I need to select all parent properties (n deep), so if you have a hierarchy Grand Parent> Parent> Child, the child will have all properties defined to Great parent / parent.

I've got pretty close, but the problem occurs when the child has no properties, it gets filtered by INNER JOIN in CTE.

Does anyone know how I can avoid this Child2 in this example, still need to have all the Grand Parent / Parent props?

I installed the Fiddle here: http://www.sqlfiddle.com/#!6/08dc3/4 but my code is below:

INSERT INTO ProductDefinition
    VALUES (1,'G. Parent'),(2,'Parent'),(3,'Child1'),(4,'Child2'),(5,'Child3');

INSERT INTO ProductDefinitionField
VALUES
  (1,1,'G. Field'),
  (2,2,'P. Field'),
  (3,3,'C. Field'),
  (4,5,'C. Field');

INSERT INTO ProductDefinitionRelation
VALUES (2,1),(3,2),(4,2),(5,2);

WITH Fields
AS
    (
        SELECT
            pd.ProductDefinitionId
          , pd.Name AS [ProductDefinitionName]
          , pdf.ProductDefinitionFieldId
          , pdf.Name AS [ProductDefinitionFieldName]
        FROM
            ProductDefinition pd
                LEFT JOIN ProductDefinitionRelation pdr ON pd.ProductDefinitionId = pdr.ProductDefinitionId
                LEFT JOIN ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId
        WHERE
            pdr.ProductDefinitionId IS NULL

    UNION ALL

        SELECT
            pd.ProductDefinitionId
          , pd.Name AS [ProductDefinitionName]
          , pdf.ProductDefinitionFieldId
          , pdf.Name AS [ProductDefinitionFieldName]
        FROM
            Fields f
                JOIN ProductDefinitionRelation pdr ON f.ProductDefinitionId = pdr.ParentProductDefinitionId
                JOIN ProductDefinition pd ON pdr.ProductDefinitionId = pd.ProductDefinitionId
                JOIN ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId

    UNION ALL

        SELECT
            pd.ProductDefinitionId
            , pd.Name AS [ProductDefinitionName]
            , f.ProductDefinitionFieldId
            , f.ProductDefinitionFieldName AS [ProductDefinitionFieldName]
        FROM
            Fields f
                JOIN ProductDefinitionRelation pdr ON f.ProductDefinitionId = pdr.ParentProductDefinitionId
                JOIN ProductDefinition pd ON pdr.ProductDefinitionId = pd.ProductDefinitionId
                JOIN ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId
)
SELECT DISTINCT
    *
FROM
    Fields
ORDER BY
    ProductDefinitionName