Sql-server – Sql parent Child Query not working

sql servert-sql

I want to get parent and their child data which means, parents first, then children.
Below is the query that I tried to convert to parent and their children

SELECT a.FactorTitle,
       a.FactorCode,
       a.parentId,
       c.FactorColumnCode,
       c.FactorColumnTitle,
       c.FactorColumnValue,
       c.isFactorValue,
       c.FieldType,

  (SELECT count(*)
   FROM FactorSetup
   WHERE parentId=a.FactorCode) AS childCount
FROM FactorSetup a
INNER JOIN PDModelSetup b ON a.PDModelCode=b.PDModelCode
LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
WHERE a.PDModelCode=2
  AND c.isFactorValue <> 'Y'
  AND a.FactorType='4'

I try below query

WITH EntityChildren AS
  (
   SELECT  a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType,

  (SELECT count(*)
   FROM FactorSetup
   WHERE parentId=a.FactorCode) AS childCount
   FROM FactorSetup a
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   UNION ALL 
   SELECT a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType,

  (SELECT count(*)
   FROM FactorSetup
   WHERE parentId=a.FactorCode) AS childCount
   FROM FactorSetup a

   INNER JOIN EntityChildren e2 ON a.parentId = e2.FactorCode
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   )

SELECT * FROM EntityChildren

After executing the query I got this error

Msg 467, Level 16, State 1, Line 1
GROUP BY, HAVING, or aggregate
functions are not allowed in the recursive part of a recursive common
table expression 'EntityChildren'.
Msg 462, Level 16, State 1, Line 1
Outer join is not allowed in the recursive part of a recursive common
table expression 'EntityChildren'.

Then I change my query and remove count(*)

WITH EntityChildren AS
  (
   SELECT  a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType
   FROM FactorSetup a
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   UNION ALL 
   SELECT a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType
   FROM FactorSetup a

   INNER JOIN EntityChildren e2 ON a.parentId = e2.FactorCode
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   )

SELECT * FROM EntityChildren

But I got this error

Msg 462, Level 16, State 1, Line 1
Outer join is not allowed in the
recursive part of a recursive common table expression
'EntityChildren'.

Best Answer

Define the outer join as a separate, non-recursive, CTE:

WITH FactorColumns AS
(
  SELECT  a.FactorTitle,
          a.FactorCode,
          a.parentId,
          c.FactorColumnCode,
          c.FactorColumnTitle,
          c.FactorColumnValue,
          c.isFactorValue,
          c.FieldType
  FROM FactorSetup a
  LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
),
...

Now just reference FactorColumns in EntityChildren – and syntactically your recursive CTE will have no outer join:

...
EntityChildren AS
(
  SELECT  f.FactorTitle,
          f.FactorCode,
          f.parentId,
          f.FactorColumnCode,
          f.FactorColumnTitle,
          f.FactorColumnValue,
          f.isFactorValue,
          f.FieldType
  FROM FactorColumns AS f

  UNION ALL 

  SELECT f.FactorTitle,
         f.FactorCode,
         f.parentId,
         f.FactorColumnCode,
         f.FactorColumnTitle,
         f.FactorColumnValue,
         f.isFactorValue,
         f.FieldType
  FROM FactorColumns AS f
  INNER JOIN EntityChildren AS e2 ON f.parentId = e2.FactorCode
)
...
Related Question