select all products and join main category through sub-categories (unknown level)

Question

I have 2 tables

Categories

id - name - parent
1 - Category A - 0
2 - Category B - 0
3 - Category C - 0
4 - Category D - 0
5 - Subcategory Of 1 - 1
6 - Subcategory Of 5 - 5
7 - Subcategory Of 5 - 5

Product

id - name - category - description
1  - Name - 5 - Description

How to select all products and join main category through sub-categories? Product categories can has only 1 or 2 or 3 or 4 levels (Unknown level).

I use "WITH RECURSIVE" in categories table but can't find the way to combine product table with 1 time query

WITH RECURSIVE category_child AS 
(
    SELECT * FROM categories as c WHERE c.id = 5
    UNION ALL
    SELECT c2.* FROM categories AS c2
    JOIN category_child as c3 ON c3.parent_id = c2.id
)

What's the best way to do this ?

Expected Result

id - name - category - description - root - sub category id 1 - sub category id 2 - sub category id 3

OR

id - name - category - description - root
id - name - category - description - sub category id 1
id - name - category - description - sub category id 2
id - name - category - description - sub category id 3

It's unclear to me what the output is you want. Please edit your question and the expected output based on your sample data. Formatted text please, no screen shots — a_horse_with_no_name
@a_horse_with_no_name Thanks i have added my expected result. — Armmydev

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2016-10-07T06:23:53

As you want the complete path to a category, you can't start your non-recursive part with c.id = 5 you have to start at the root using where parent_id is null (you should not identify the root nodes with a non-existing category ID, that prevents creating a proper foreign key for the parent_id column).

In the recursive part you can then aggregate the full path to the root category:

with recursive tree as 
(
  select *, id as root_category, concat('/', name) as category_path
  from category
  where parent_id is null
  union all
  select c.*, p.root_category, concat(p.category_path, '/', c.name)
  from category c
    join tree p on c.parent_id = p.id
)
select p.id as product_id,
       p.name as product_name,
       t.root_category,
       t.category_path
from tree t
  join product p on p.category = t.id

Using the following sample data:

create table category (id integer, name text, parent_id integer);
create table product (id integer, name text, category integer, description text);

insert into category
values
(1, 'Category A', null),
(2, 'Category B', null),
(3, 'Category C', null),
(4, 'Category D', null),
(5, 'Subcategory Of 1', 1),
(6, 'Subcategory Of 5', 5),
(7, 'Subcategory Of 5', 5),
(8, 'Subcategory of D', 4)
;

insert into product
values
(1, 'Product One', 5, 'Our first product'),
(2, 'Product Two', 8, 'The even better one');

This returns:

product_id | product_name | root_category | category_path               
-----------+--------------+---------------+-----------------------------
         1 | Product One  |             1 | /Category A/Subcategory Of 1
         2 | Product Two  |             4 | /Category D/Subcategory of D

select all products and join main category through sub-categories (unknown level)

1 Answers