clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36284 in the last week).

CREATE TABLE category (id int, name text, parent_id int); INSERT INTO category (id, name, parent_id) VALUES (1, 'name1', NULL), (2, 'name2', 1), (3, 'name3', 2), (4, 'name4', 1), (5, 'name5', 4), (6, 'name6', NULL); CREATE TABLE cat_prod (id int, product_id int, category_id int); INSERT INTO cat_prod (id, product_id, category_id) VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1), (4, 3, 1), (5, 4, 2), (6, 5, 2), (7, 6, 3); -- ? product 6 is missing on your demo sample? CREATE TABLE product (id int, name text); INSERT INTO product (id, name) VALUES (1, 'name1'), (2, 'name2'), (3, 'name3'), (4, 'name4'), (5, 'name5');
6 rows affected
7 rows affected
5 rows affected
 hidden batch(es)


-- func to return id of category and all children CREATE OR REPLACE FUNCTION f_cats_of_id(int) RETURNS TABLE (category_id int) AS $func$ WITH RECURSIVE cte AS ( SELECT $1 AS parent_id -- not checking existence, cheapest UNION ALL SELECT c.id FROM cte JOIN category c USING (parent_id) ) TABLE cte $func$ LANGUAGE SQL;
 hidden batch(es)


SELECT DISTINCT p.* FROM f_cats_of_id(1) c JOIN cat_prod cp USING (category_id) JOIN product p ON p.id = cp.product_id;
id name
3 name3
5 name5
1 name1
2 name2
4 name4
 hidden batch(es)