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