1

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 
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 *

  • There is not root_id in the table, only the category's parent_id. (I edited the question to include the table). Good tip on the CTE. – Todd Oct 1 '15 at 12:47
0

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
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.