By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE categories (id int, cat_name varchar(100),parent_category_id int DEFAULT NULL);
INSERT INTO categories VALUES
(1, 'Mens', NULL)
,(2, 'Tops', 1)
,(3, 'Jerseys', 2)
,(4, 'England-1', 3)
,(5, 'England-2', 3)
;
select * from categories;
Records: 5 Duplicates: 0 Warnings: 0
| id | cat_name | parent_category_id |
|---|---|---|
| 1 | Mens | null |
| 2 | Tops | 1 |
| 3 | Jerseys | 2 |
| 4 | England-1 | 3 |
| 5 | England-2 | 3 |
WITH RECURSIVE category_tree AS (
SELECT 0 as lvl, id, cat_name, parent_category_id,
cat_name AS full_name
FROM categories
WHERE parent_category_id IS NULL
UNION ALL
SELECT lvl+1 as lvl,c.id, c.cat_name,c.parent_category_id,
CONCAT (ct.full_name, ' > ', c.cat_name)
FROM category_tree ct
INNER JOIN categories c
ON c.parent_category_id = ct.id
)
SELECT * -- id, full_name
FROM category_tree;
| lvl | id | cat_name | parent_category_id | full_name |
|---|---|---|---|---|
| 0 | 1 | Mens | null | Mens |
| 1 | 2 | Tops | 1 | Mens > Tops |
| 2 | 3 | Jerseys | 2 | Mens > Tops > Jerseys |
| 3 | 4 | England-1 | 3 | Mens > Tops > Jerseys > England-1 |
| 3 | 5 | England-2 | 3 | Mens > Tops > Jerseys > England-2 |
WITH RECURSIVE category_tree AS (
SELECT 0 as lvl, id, cat_name, parent_category_id,
cat_name AS full_name
FROM categories
WHERE parent_category_id IS NULL
UNION ALL
SELECT lvl+1 as lvl,c.id, c.cat_name,c.parent_category_id,
CONCAT (ct.full_name, ' > ', c.cat_name)
FROM category_tree ct
INNER JOIN categories c
ON c.parent_category_id = ct.id
where lvl<0
)
SELECT * -- id, full_name
FROM category_tree;
| lvl | id | cat_name | parent_category_id | full_name |
|---|---|---|---|---|
| 0 | 1 | Mens | null | Mens |
WITH RECURSIVE category_tree AS (
SELECT 0 as lvl, id, cat_name, parent_category_id,
cat_name AS full_name
FROM categories
WHERE parent_category_id IS NULL
UNION ALL
SELECT lvl+1 as lvl,c.id, c.cat_name,c.parent_category_id,
CONCAT (ct.full_name, ' > ', c.cat_name)
FROM category_tree ct
INNER JOIN categories c
ON c.parent_category_id = ct.id
where lvl<1
)
SELECT * -- id, full_name
FROM category_tree;
| lvl | id | cat_name | parent_category_id | full_name |
|---|---|---|---|---|
| 0 | 1 | Mens | null | Mens |
| 1 | 2 | Tops | 1 | Mens > Tops |
WITH RECURSIVE category_tree AS (
SELECT 0 as lvl, id, cat_name, parent_category_id,
cat_name AS full_name
FROM categories
WHERE parent_category_id IS NULL
UNION ALL
SELECT lvl+1 as lvl,c.id, c.cat_name,c.parent_category_id,
CONCAT (ct.full_name, ' > ', c.cat_name)
FROM category_tree ct
INNER JOIN categories c
ON c.parent_category_id = ct.id
where lvl<2
)
SELECT * -- id, full_name
FROM category_tree;
| lvl | id | cat_name | parent_category_id | full_name |
|---|---|---|---|---|
| 0 | 1 | Mens | null | Mens |
| 1 | 2 | Tops | 1 | Mens > Tops |
| 2 | 3 | Jerseys | 2 | Mens > Tops > Jerseys |
WITH RECURSIVE category_tree AS (
SELECT 0 as lvl, id, cat_name, parent_category_id,
cat_name AS full_name
FROM categories
WHERE parent_category_id IS NULL
UNION ALL
SELECT lvl+1 as lvl,c.id, c.cat_name,c.parent_category_id,
CONCAT (ct.full_name, ' > ', c.cat_name)
FROM category_tree ct
INNER JOIN categories c
ON c.parent_category_id = ct.id
-- where lvl<3
)
SELECT * -- id, full_name
FROM category_tree;
| lvl | id | cat_name | parent_category_id | full_name |
|---|---|---|---|---|
| 0 | 1 | Mens | null | Mens |
| 1 | 2 | Tops | 1 | Mens > Tops |
| 2 | 3 | Jerseys | 2 | Mens > Tops > Jerseys |
| 3 | 4 | England-1 | 3 | Mens > Tops > Jerseys > England-1 |
| 3 | 5 | England-2 | 3 | Mens > Tops > Jerseys > England-2 |