What I need
I need select all ids or names from table "category" (all chidrens) until category_id is 0 (main category).
Example On web there is product with category tree.
In table "category" I have parent category id as 0, and other children categories ids are defined by numbers as this.
How can I in sql show all categories tree in one field? For example.
Úvod > Bydlení > Barvy a Laky > Vnitřní barvy
What is the simplest way to do that?
DBMS = PostgreSQL ver. 10.9.
Thank you very much.
Edited, updated, for ids main category id is not present.
By Akina solution.
with recursive cte as( select category_id,category_parent,category_name::text from s_category where category_parent = 0 union all select s_category.category_id, s_category.category_parent, concat(cte.category_name, ' > ', s_category.category_name) from s_category,cte where cte.category_id = s_category.category_parent ), cte2 as( select category_id,category_parent,category_name::text from s_category where category_parent = 0 union all select s_category.category_id, s_category.category_parent, concat(cte2.category_id, ' : ', s_category.category_id) from s_category,cte2 where cte2.category_id = s_category.category_parent ) select cte.category_name as tree_category, cte2.category_id as tree_id, s_product.product_id as itemID, s_product.product_id as itemGroupID, product_shop_id as productCode, product_ean as ean, product_name as title, product_short_label as description, product_url as link, filename as image, price_tax as price, price_rec as priceOriginal, producer_name as brand from s_product left join s_product_image on s_product.product_id = s_product_image.product_id left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id left join s_producer on s_product.producer_id = s_producer.producer_id left join cte on s_product.category_id = cte.category_id left join cte2 on s_product.category_id = cte2.category_id where s_product.product_id = '133471'