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