Include parent node ID in hierarchical recordset of child rows

Question

I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a specified parent, but I would like to also include the parent id on each row.

WITH level_1 (category_id, category_name) AS (
  SELECT
   category_id, 
   category_name
  FROM category
  WHERE 
   parent_category_id = 1
  UNION ALL
  SELECT 
    c.category_id,
    c.category_name
  FROM category AS c CROSS JOIN level_1 
  WHERE 
    c.parent_category_id = level_1.category_id
)
select * from level_1

The query as written returns the following:

category_id | category_name
         64 | Cows
         6  | Pigs
         11 | Holstein
         23 | Bantu

What I want though is below because I want to group on those parent categories but I won't necessarily know what they are except that they have a parent_category_id = 1.

 root_id | category_id |  category_name
      64 |          64 |  Cows
       6 |           6 |  Pigs
      64 |          11 |  Holstein
       6 |          23 |  Bantu

Category table is like below.

   category_id         |  unique row identifier
   category_name       |  varchar
   category_parent_id  |  foreign key to category table 

Answer 1

It's hard to understand your requirements without seeing the definition of category - Presumably, there is a root_id column in that table?

If there is, perhaps you want this?

;WITH level_1 (category_id, category_name, root_id) AS (
    SELECT category_id, 
        category_name,
        root_id
    FROM dbo.category
    WHERE parent_category_id = 1
    UNION ALL
    SELECT c.category_id,
        category_name,
        root_id
    FROM dbo.category AS c 
    CROSS JOIN level_1 
    WHERE c.parent_category_id = level_1.category_id
)
SELECT category_id
    , category_name
    , root_id 
FROM level_1;

Just FYI, it's good practice to begin a CTE with a semicolon, to specify the schema, and avoid using SELECT *

Answer 2

The only way I was able to solve this problem was to utilize a union of multiple self joins instead of the recursive CTE. Of course this only works on a finite number of nestings but in my case this was OK.

SELECT DISTINCT 
  c1.category_id AS category_id , 
  c1.category_name AS root_name,
  c1.category_id AS root_id 
FROM 
  category AS c0
  INNER JOIN category AS c1 ON c0.category_id = c1.parent_category_id
  WHERE c0.parent_category_id IS NULL

UNION ALL

SELECT DISTINCT 
  c2.category_id AS category_id, 
  c1.category_name AS root_name,
  c1.category_id AS root_id 
FROM category AS c0
  INNER JOIN category AS c1 ON c0.category_id = c1.parent_category_id
  INNER JOIN category AS c2 ON c1.category_id = c2.parent_category_id
WHERE c0.parent_category_id IS NULL

...

Above shows 1 level of nesting. For each subsequent level, add a copy of the prior block and inserting another inner join as:

Select
  Cn.category_id as category_id,
...
  inner join Cn ON c(n-1).category_id = Cn.parent_category_id
...